search

simonw / db-to-sqlite

CLI tool for exporting tables or queries from any SQL database to a SQLite file
Apache License 2.0
370 stars 29 forks source link

TZ flag for migrating datetime columns? #38

Open cunla opened 2 years ago

cunla commented 2 years ago

In my original DB, all datetime fields are in UTC. It should be possible (using a flag perhaps?) to convert the datetimes to be without timezone in sqlite3? Right now the resulting value causes a few issues.

Sample postgres table (partial):

id,created_at
2,2021-07-30 11:44:09.984439 +00:00
3,2021-07-30 11:44:29.217916 +00:00
4,2021-07-30 11:44:43.598702 +00:00
5,2021-08-03 20:53:48.482419 +00:00
6,2021-08-04 22:19:52.810907 +00:00
7,2021-08-05 17:25:29.646553 +00:00
8,2021-08-05 17:25:33.425523 +00:00
9,2021-08-05 17:26:22.169369 +00:00

Sample result table:

2,2021-07-30T11:44:09.984439+00:00
3,2021-07-30T11:44:29.217916+00:00
4,2021-07-30T11:44:43.598702+00:00
5,2021-08-03T20:53:48.482419+00:00
6,2021-08-04T22:19:52.810907+00:00
7,2021-08-05T17:25:29.646553+00:00
8,2021-08-05T17:25:33.425523+00:00
9,2021-08-05T17:26:22.169369+00:00

Expected format:

285,2021-11-29 12:40:20.021350
286,2021-11-29 12:40:20.023282
287,2021-11-29 12:40:20.024771
288,2021-11-29 12:40:20.026995