Python's sqlite3 library sometimes returns naive datetimes and sometimes aware datetimes for th-CodePudding

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?

CodePudding user response：

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.