Created
August 6, 2024 10:30
-
-
Save innermond/12205ab496d429fd755513896f66e79e to your computer and use it in GitHub Desktop.
If you send a date time (without timezone specifier) to a SQLite database they will be saved as UTC (not as your local time). The following query corrects this issue.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --- localtime of sqlite MUST be the same with app that sent incorrect touched_date | |
| with param as ( | |
| select | |
| touched_date as v, timediff(datetime(touched_date, 'localtime'), datetime(touched_date)) as delta from <errored-table-name>) | |
| select | |
| delta, | |
| datetime(v), | |
| CASE WHEN SUBSTR(delta, 1, 1) = '+' | |
| THEN datetime(v, REPLACE(delta, '+', '-')) | |
| ELSE datetime(v, REPLACE(delta, '-', '+')) | |
| END | |
| from | |
| param; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment