Beats a swift kick in the pants!

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.

It looks like this works, but it's pretty gnarly.

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

You can try the query out here: https://github-to-sqlite.dogsheep.net/github?sql=select+auth...

On the one hand: very impressive!

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).

Hah, yeah PST v.s. PDT does make this harder.

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

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 :).

I just wrote something using https://github.com/SqliteModernCpp/sqlite_modern_cpp, which I chose after 10 minutes of searching around. Should I have used yours instead? How should I decide?

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).

The ISO8601 weekdate format[1] is tricky, but it can be done:

    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 ;

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 :).

[1] https://en.wikipedia.org/wiki/ISO_week_date

Re the annoying part, a different way to get the day of week in the rance 1–7 is

  1+(6+strftime('%w',yyyymmdd))%7

Without a timezone the time is meaningless for many purposes.

We store 8 million transactions a day in UTC...customer time zone is merely an integer offset stored alongside...simple and easy, what’s wrong with that?

The customer's offset is always changing but I assume you mean you're storing the offset as it was at the moment of the transaction.

How 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?

For daylight savings, the offset is generated in real-time in our app at the time of transaction and then stored inside the db...so if 2x TXs occurred in CST at 1:58am (1 before and 1 after DST went into effect at 2am) they would both show up as being between 1-2am on the day of time change. However, the spike in data would show the anomaly for our use case. For a more mission critical app, you could have an additional tinyint field that would get set as 1 for all transactions that occurred in these transitional periods to be able to show data with or without the daylight savings.