sqlite> select datetime('now', '-90 days');
2020-12-12 21:44:22
You're right, that was a bad example. Maybe it's just me, but I've never figured out how to get SQLite to do a query like "select from orders where the order was on a Tuesday in Pacific time". I don't think you can; that requires predicates, and all I see are strftime and some useful pre-cooked variations on it.
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.
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
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 :).
1+(6+strftime('%w',yyyymmdd))%7How do you deal with the twice a year time offsets when a transaction occured first but shows up after because the clock rolled back an hour?
Isn't it generally better to store to the full timezone like America/New_York?