On the other hand: Wrong from March 14th to November 7th of this year.
This is where a richer standard library of datetime functions would come in handy. Another example: strftime's %W starts on the first Monday of the year, when the ISO week calendar starts the first Monday of the week containing the first Thursday (so the first week is sometimes found in the previous calendar year).
At that point I'd switch to using additional custom SQL functions. I wrote a plugin called datasette-dateutil https://datasette.io/plugins/datasette-dateutil that exposes some extra functions, but it doesn't have anything useful for timezone calculations yet.
I've opened an issue: https://github.com/simonw/datasette-dateutil/issues/4
// db inited by SqliteModernCpp
sqlite::database db(":memory:");
{
sqlite3cpp::database attached_db(db.connection().get());
// create the SQL function as you like
attached_db.create_scalar("mutiply", [](int x, int y) {
return x * y;
});
} // attached_db is going out of scope
// SQL scalar function `multiply` is still availble
db << "CREATE TABLE numbers (n1 INTEGER, n2 INTEGER);";
db << "SELECT multiply(n1, n2) from numbers;";
SELECT strftime('%Y', yyyymmdd, '-3 days', 'weekday 4') || '-W' || substr('00' || (((strftime('%j', date(yyyymmdd), '-3 days', 'weekday 4')) - 1) / 7 + 1), -2, 2) || '-' || replace(strftime('%w', yyyymmdd), '0', '7') AS yyyywwwd ;
If sqlite supported macros, we could have put the entire expression in a new "function". But you could (ab)use m4 or even the C preprocessor for that :).
I have datetimes stored in UTC, so I first needed to convert them to PST by applying the 8 hour time difference.
Then I used strftime('%w') to get the day of week (as a number in a string).
Then I can filter for that equalling '4' for Thursday.
You can try the query out here: https://github-to-sqlite.dogsheep.net/github?sql=select+auth...