Fixing sqlite TIMESTAMP converter to handle UTC offsets

The converter for TIMESTAMP columns that the sqlite3 module automatically registers ignores UTC offsets and always returns a naive datetime object.

I filed bpo-45335, and erlendaasland suggested deprecating the current behavior and switching to a timezone-aware converter in Python 3.13. sqlite3’s custom datetime parser can be replaced with datetime.datetime.fromisoformat, which was added in Python 3.7 and handles UTC offsets properly.

I am starting a discussion here to solicit feedback on this proposal. I think it is the best solution, but it should be kept in mind that changing the TIMESTAMP converter is backwards-incompatible and could result in some very confusing errors (like this), so we should make sure to give users plenty of advance warning if this is the route we choose to take.

1 Like

I don’t think this is a good way forward.

SQLite does not have a date/time storage type. Instead you can use a text field
(ISO, with/without UTC offset), a real (JDN) or an integer (Unix ticks):

https://sqlite.org/datatype3.html#date_and_time_datatype

Only the text field format can have a timezone associated with the value, which
is then taken into account by the SQLite date/time functions, but in most cases,
the calculations are then done using UTC. SQLite assumes that date/time string
values without offset are UTC times and returns UTC for its date/time functions:

https://sqlite.org/lang_datefunc.html

but it doesn’t even use a UTC offset in cases where it should (e.g. when using
datetime(‘now’, ‘localtime’)).

The problem with all this is that data read into a database will often not carry
timezone information. It can be local time, UTC or some other time zone, which
can only be deducted from related context (e.g. origin of data, source
conventions, separate database fields, etc.).

The naive datetime type is meant for exactly those cases.

Now, you could argue that having an offset is an indicator that a specific
timezone is given, which is correct, but what do you then do with time values
which do not carry this extra information ? The only correct way to go about it
is to return a naive datetime instance in those cases, creating issues when the
data has mixes of values stored with and without time offset.

IMO, the decision should be left to the application. The current default is the
most correct in terms of not creating problems downstream. Had there been major
problems with the default, this would have been raised many years ago.

People who do need the UTC offset interpretation can register a different
converter and apply extra logic based on the application context to fill in the
gaps for values which do not have a UTC offset in the database.

PS: Best practice is to always store datetime values as UTC in a database and
then use a separate field to hold the origin timezone string / location (where
needed). This causes the least surprises and errors. It can also be faster,
depending on how the database stores these values.

1 Like

Good points, @malemburg. I withdraw my suggestion.

I understand your point, but it seems like a basic invariant is being violated here, which is that writing back the same data that I read from the database should not result in data loss.

It seems to me that if application programmers want to ignore UTC offsets which are in the database, then that should be the opt-in choice, rather than the other way around.

I 100% agree with that, however the problem I see with Python’s naive datetime is that it’s not assuming UTC but local time. That’s why I originally though that parsing the UTC offset correctly would be a good idea.

Something else, I forgot to mention: sqlite3 will only use those converters if you pass in detect_types=sqlite3.PARSE_DECLTYPES to the .connect() method. Converters are not used per default. If you do want to use converters, it’s typically best to provide your own implementations, as the default ones may or may not be exactly what you want. E.g. the timestamp converter doesn’t even work with time values which have ISO time offset deltas.

Without detect_types, sqlite3 returns strings for e.g. timestamp columns.

1 Like

See also:

  • bpo-19065: sqlite3 timestamp adapter chokes on timezones
  • bpo-26651: Deprecate register_adapter() and register_converter() in sqlite3

cc. @berkerpeksag

In the meantime, can I add a line to the sqlite3 docs noting that the TIMESTAMP converter ignores UTC offsets?

1 Like

IMO, yes that’s an improvement. See the devguide on how to add notes and warnings to the docs:

https://devguide.python.org/documenting/#paragraph-level-markup