This came up as a question on Hacker News. How can you query a SQLite database for items that were created on a Thursday in PST, when the data is stored in UTC?
I have datetimes stored in UTC, so I first needed to convert them to PST by applying the 8 hour time difference, using
datetime(author_date, '-8 hours') as author_date_pst.
Then I used
strftime('%w') to get the day of week (as a number contained in a string).
Then I can filter for that equalling '4' for Thursday.
select author_date, datetime(author_date, '-8 hours') as author_date_pst, strftime('%w', datetime(author_date, '-8 hours')) as dayofweek_pst, * from commits where dayofweek_pst = '4' -- Thursday
Try this query.
SQLite documentation for date time functions is at https://sqlite.org/lang_datefunc.html
Created 2021-03-12T19:55:21-08:00 · Edit