1

I have a TIMESTAMP column in my SQLite database storing a datetime with a timezone, e.g. 2021-09-29 18:46:02.098000+00:00. When I fetch this row inside my Django app, the column is returned as an aware datetime object. However, when I fetch this row in a script that doesn't use Django, in the exact same code path, the column is returned as a naive object.

Note that in both cases I am using the built-in sqlite3 library, not Django's ORM.

Why are the return types inconsistent?

iafisher
  • 938
  • 7
  • 14

1 Answers1

2

Django ships with a built-in SQLite converter for TIMESTAMP columns that overrides the built-in Python converter, and, unlike the built-in one, return aware datetimes if the database column contains a time zone.

Since the converter is registered globally, it applies regardless of whether you are using the Django ORM or not.

To override Django's converter, you can call sqlite3.register_converter in the ready method of one of your apps, e.g.:

from django.apps import AppConfig

class MyAppConfig(AppConfig):
    name = "myapp"

    def ready(self):
        sqlite3.register_converter("TIMESTAMP", my_converter)

Python's sqlite3 doesn't expose its naive timestamp converter as a public method, but it is short enough that you can copy-paste it from the source.

Alternatively, you can register a time zone-aware converter in your non-Django code, e.g. using datetime.datetime.fromisoformat.

The converter is registered in the Django codebase here, and the original ticket with some discussion is available here.

iafisher
  • 938
  • 7
  • 14
  • I wonder why the built-in converter doesn't attempt to use `datetime.fromisoformat` - which would set the UTC offset and return aware datetime – FObersteiner Sep 30 '21 at 13:13
  • Perhaps it is simply an oversight, given that `datetime.fromisoformat` was only added in Python 3.7. I filed a bug against CPython: https://bugs.python.org/issue45335 – iafisher Sep 30 '21 at 19:38
  • Well, for legacy code <3.7 I'd expect that people are handling their UTC offsets (*time zones* is another story) in some way - which, if it expects naive datetime input, might break if aware datetime is returned after an update. So this might be more complicated than I thought at first. Updates to the standard lib should avoid breaking changes I guess, so this was a design flaw in the first place if you ask me. – FObersteiner Oct 01 '21 at 06:31