Architectural Decision Records

001: How are datetimes handled?

Datetimes are traditionally serialized in one of two ways:

  1. An integer number of seconds (or fractional seconds) from some epoch, usually the unix epoch.

  2. An iso-8601-formatted string (or RFC-2822, but similar concept).

Both are perfectly valid, but let’s consider the context of sqlite:

  1. Sometimes you fire up sqlite3 ./path/to/db to debug something, and it’s nice to be able to see the datetimes readibly when select *-ing them.

  2. Sqlite datetime functions, by default, assume that an integer or floating point value is a julian day number, not a unix timestamp.

To avoid mistakes, the string is better in this case.

Now, let’s think about timezones. Sometimes you wish you could abolish them, eh?

The typical parroted advice is “just use UTC for everything”. This library was originally made to keep track of future events for a specific locale. Not only is it unnecessary to keep track of the timezone, it could be fully incorrect to do so:

So what happened was that your software got the newest timezone update. And then when converting back from UTC it used the new rules, while the saved UTC time (14:00) were based on the old rules. With the old rules you would have to subtract 4 hours, but with the new rules only 3. And subtracting 3 hours from 14:00 you get 11:00 instead of 10:00.

This story is not pure fantasy. You might not be going to Chile in April. But a lot of software actually works this way and the time zone in Chile is actually announced to be changing as described. … Instead of saving the time in UTC along with the time zone, developers can save what the user expects us to save: the wall time.

That’s a great blog post, seriously, go read it when you have the chance.

As The Zen of Python says, “In the face of ambiguity, refuse the temptation to guess.”

We don’t offer any type-dispatched converters for temporal types by default. However, we put in easy-to-use named ones. And adding your own is trivial– we even expose those same converters for you to add with a single line of code.

002: Do we keep named converters?

While writing the docs for conversion, I realized it would be far simpler to just expose conversion functions, and let people override typed conversions themselves.

After all, if you really need two bindings to have the same value after a function call, you can just assign them to a variable. Right?

If we value making the line between python and sql seamless, we must not do this.

Conversion is an implementation detail of the underlying DB. By forcing the conversion invocations to happen in “python land”, we’re making the users manually handle implementation details!

Sure, naming it at the interpolation site is still sorta exposing it, but that pushes the syntax for it into the query, where it’s “sql land”.

Plus, this lets us accumulate errors in parallel!

Todo

However… it does mean you don’t get type hints on the converters. Maybe we advocate for making named functions that create analyzed queries.

003: Why not use sqlite3’s built-in converter and adapter API?

Those are global, not per-connection. And it’s overall unergonomic.

003.1: Addendum: What about the PEP 246 adapation protocol?

The PEP was rejected, so it’s weird that the stdlib supports it.