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.

1
select
2
  author_date,
3
  datetime(author_date, '-8 hours') as author_date_pst,
4
  strftime('%w', datetime(author_date, '-8 hours')) as dayofweek_pst,
5
  *
6
from
7
  commits
8
where
9
  dayofweek_pst = '4' -- Thursday

Try this query.

SQLite documentation for date time functions is at https://sqlite.org/lang_datefunc.html