How to convert timestamp in sqlite to datetime in postgres

[huy-lv]

Hey, I'm going to migrate database from sqlite to postgres, but got some problems. The createdAt field in sqlite is unix timestamp format (eg: 1692666514210), and I want to convert to datetime type in postgres. By default, when I run the command:

sequel -C sqlite:///Users/backup/data.db postgres://postgres@localhost/test

It converts 1692666514210 to 55608-6-19 08:08:34+07
And my expectation is: 2023-08-22 08:08:34+07

[tycooon]

It looks like sequel is treating the value as seconds while it is stored in milliseconds.
You can try converting timestamps to seconds by dividing by 1000:

> Time.at(1692666514210 / 1000.0).utc
=> 2023-08-22 01:08:34 220201/1048576 UTC

[huy-lv]

please show me the full file to migrate database sqlite to postgres, I have installed sequel but I don't familiar with ruby

[jeremyevans]

SQLite does not support date/time columns natively. Your SQLite database stores timestamps in a way that Sequel does not support by default. If you aren't familiar with ruby, I recommend using a different tool than Sequel to do the conversion.