Querying for items stored in UTC that were created on a Thursday in PST

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

Querying for items stored in UTC that were created on a Thursday in PST

Related