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.
If C++ is your thing,
https://github.com/yangacer/sqlite3cpp
might be interesting. It intelligently wraps C++ functions as SQL functions.
Author here and looks forward feedbacks :).
The good news is that you could use both libraries. `sqlite3cpp::database` supports "attaching" mode that won't close a `sqlite3` instance after sqlite3cpp::database going out of scope. Here is an example for coexisting with SqliteModernCpp:
// 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;";
Note that SqliteModernCpp provides customization of SQL function as well as sqlite3cpp. Though it doesn't seem to have aggregate function supporting
(i.e. sqlite3cpp::database::create_aggregate).
returns ISO weekdate format ('2021-W10-6') the when the input column `yyyymmdd` is in year-month-day format ('2021-03-13'). Not pretty, but it works. The annoying part for me is actually that '%w' returns 0 for Sunday, 1-6 for Mon-Sat, rather than the ISO 1-7 for Mon-Sun.
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 :).
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).