11.10 Values in and out: adapters, converters and time zones

A datetime on its way in

A datetime goes into a statement through DateTimeField.get_db_prep_value, which prepares the value, unless told it is prepared, and returns what the operations object’s adapt_datetimefield_value makes of it; what comes before that is A field: the base class’s.

In the recordings on this page, made on SQLite with USE_TZ on, a line at the left margin is Python as it ran, with the calls it set going that the passage is about nested under it as they were made, and -> before what a line or a call returned; a line SQL is a statement as Django handed it to its cursor, and a line that begins sqlite the statement as SQLite ran it. Bram, a sailor of the crew, is given a new time of signing on, an aware datetime in Oslo’s time zone, and saved:

bram.signed_on = datetime.datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo('Europe/Oslo'))
bram.save(update_fields=['signed_on'])
  DateTimeField.get_db_prep_value(datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')), prepared=False)
    DatabaseOperations.adapt_datetimefield_value(datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')))
      make_naive(datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')), datetime.timezone.utc)  ->  datetime(2026, 3, 14, 17, 30)
      -> '2026-03-14 17:30:00'
  SQL UPDATE "crew_sailor" SET "signed_on" = %s WHERE "crew_sailor"."id" = %s with params ('2026-03-14 17:30:00', 2)
  sqlite UPDATE "crew_sailor" SET "signed_on" = '2026-03-14 17:30:00' WHERE "crew_sailor"."id" = 2

SQLite’s adapter makes an aware datetime naive in the connection’s time zone, here UTC, so that 18:30 in Oslo became 17:30, and returns str of it, which the driver binds and SQLite stores. The text in the column carries no time zone. That it is UTC is known only to the connection’s BaseDatabaseWrapper.timezone, and the way out depends on it.

The operations object’s adapters

BaseDatabaseOperations has an adapter for a date, a time, a datetime, a duration, a decimal, an integer, an IP address and a JSON value, and the field class of that kind calls it from its override of Field.get_db_prep_value, as DateField does. The base class’s adapters are the plain case: a date, a time and a datetime become str of the value, an aware time being refused; a duration becomes its whole number of microseconds; an integer passes as it is; an empty IP address becomes None; a JSON value becomes json.dumps of it with the field’s encoder. BaseDatabaseOperations.adapt_decimalfield_value is handed the field’s digits and places with the value and returns the value, and no backend overrides it. Where a driver wants something other than the plain case, its backend overrides the adapter:

UUIDField.get_db_prep_value uses no adapter: where BaseDatabaseFeatures.has_native_uuid_field is true, as on PostgreSQL and MariaDB, it hands down the uuid.UUID, and elsewhere the string of its 32 hexadecimal digits. BinaryField.get_db_prep_value wraps its bytes in the driver’s constructor Binary, which PEP 249 has every driver provide.

An automatic key passes through no adapter, but on a save it passes through BaseDatabaseOperations.validate_autopk_value, called from AutoFieldMixin.get_db_prep_save: the base class’s returns the value, and MySQL’s refuses 0 unless the server’s SQL mode allows it, as MySQL and MariaDB tells.

For a value with no field behind it there is BaseDatabaseOperations.adapt_unknown_value, which goes by the value’s type alone: a datetime, tested before a date since it is one, a date, a time and a decimal each to its adapter, and anything else back as it is. Its one caller is the RawQuery behind QuerySet.raw, which passes each of its parameters through it, since the type each is meant for is not known; a datetime given to a raw query reaches the adapter a field’s would:

signed = list(Sailor.objects.raw('SELECT id, name FROM crew_sailor WHERE signed_on > %s ORDER BY id', [datetime.datetime(2026, 1, 1, tzinfo=zoneinfo.ZoneInfo('Europe/Oslo'))]))
  RawQuery._execute_query
    BaseDatabaseOperations.adapt_unknown_value(datetime(2026, 1, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')))
      DatabaseOperations.adapt_datetimefield_value(datetime(2026, 1, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')))

What the driver does with a parameter

SQLite’s backend registers adapters with the driver, through sqlite3.register_adapter, as django.db.backends.sqlite3.base is imported: str for a decimal.Decimal, adapt_date for a datetime.date, which writes its ISO form, and adapt_datetime for a datetime.datetime, which writes the ISO form with a space before the time. They are registered on the module sqlite3, not on a connection. A field’s datetime never reaches them, being a string already; one handed to Django’s cursor does, for on SQLite that cursor passes it on unchanged:

with connection.cursor() as cursor: cursor.execute('SELECT %s', [datetime.datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo('Europe/Oslo'))]); row = cursor.fetchone()
  SQL SELECT %s with params [datetime.datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo'))]
  adapt_datetime(datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo')))  ->  '2026-03-14 18:30:00+01:00'

adapt_datetime knows nothing of time zones: it wrote the value with its offset, where the operations object’s adapter would have made it naive in the connection’s zone.

PostgreSQL’s operations object hands dates, times, datetimes and durations down as they are, for psycopg to bind. Oracle’s cursor, beneath Django’s, wraps each parameter in an OracleParam, which with USE_TZ on makes a datetime an Oracle_datetime, a class whose docstring says it tells the driver to save the microseconds too, and which turns True and False into 1 and 0 on a server older than 23; Oracle has the cursor.

A datetime on its way out

A datetime column Django made comes back from SQLite through two conversions, the driver’s and the backend’s; Bram’s time of signing on, read back:

signed_on = Sailor.objects.values_list('signed_on', flat=True).get(name='Bram')
  SQL SELECT "crew_sailor"."signed_on" AS "signed_on" FROM "crew_sailor" WHERE "crew_sailor"."name" = %s LIMIT 21 with params ('Bram',)
  sqlite3.Cursor.fetchmany
    decoder(parse_datetime)'s lambda(b'2026-03-14 17:30:00')
      parse_datetime('2026-03-14 17:30:00')  ->  datetime(2026, 3, 14, 17, 30)
  sqlite3.Cursor.fetchmany
  SQLCompiler.get_converters
    DatabaseOperations.get_db_converters(Col(crew_sailor, crew.Sailor.signed_on))  ->  [DatabaseOperations.convert_datetimefield_value]
    Field.get_db_converters
    -> {0: [DatabaseOperations.convert_datetimefield_value]}
  SQLCompiler.apply_converters
    DatabaseOperations.convert_datetimefield_value(datetime(2026, 3, 14, 17, 30))
      make_aware(datetime(2026, 3, 14, 17, 30), datetime.timezone.utc)  ->  datetime(2026, 3, 14, 17, 30, tzinfo=UTC)
      -> datetime(2026, 3, 14, 17, 30, tzinfo=UTC)
  SQLCompiler.apply_converters resumes

The first conversion is the driver’s, inside sqlite3.Cursor.fetchmany. SQLite’s backend opens each connection with "detect_types" set to sqlite3.PARSE_DECLTYPES and sqlite3.PARSE_COLNAMES unless the alias’s "OPTIONS" give another value; with them, the driver calls the converter registered under the name of the type each column was declared with, with the column’s raw bytes. The backend registers converters with sqlite3.register_converter as its module is imported: the method __eq__ of b"1" under "bool", true for those bytes and false for any others, and under "date", "time", "datetime" and "timestamp" a decoder around one of Django’s parsers, which decodes the bytes to a string first. The first four names are the types SQLite’s DatabaseWrapper.data_types writes for a boolean, a date, a time and a datetime field (Column types: from a field to a column), so a column Django made for one of those four comes back parsed.

A column with no declared type, such as a function’s, is not converted by the driver, and comes back as SQLite stores it: a datetime as text, a boolean as an integer. SQLite’s converters take values of both kinds: DatabaseOperations.convert_datetimefield_value parses a value that is not yet a datetime and leaves one the driver parsed, and the converters for dates and times do the same.

The second conversion is the backend’s. The compiler gathers, for each column, the operations object’s converters and then the field’s, and applies them to each row in that order; The compiler: as_sql, execute_sql and results_iter tells it. SQLite’s DatabaseOperations.get_db_converters chooses by the internal type of the column’s output field. Here it named convert_datetimefield_value, which with USE_TZ on made the value aware in the connection’s zone and so gave back the instant that went in.

Booleans, decimals and the other kinds of value

A boolean and a date read on SQLite, a Manifest’s stamped and a Voyage’s sailed:

stamped = Manifest.objects.values_list('stamped', flat=True).get()
...
  DatabaseOperations.convert_booleanfield_value(False)  ->  False
...
sailed = Voyage.objects.values_list('sailed', flat=True).get(pk=1)
  SQL SELECT "fleet_voyage"."sailed" AS "sailed" FROM "fleet_voyage" WHERE "fleet_voyage"."id" = %s LIMIT 21 with params (1,)
  decoder(parse_date)'s lambda(b'2026-03-02')
    parse_date('2026-03-02')  ->  date(2026, 3, 2)
  DatabaseOperations.convert_datefield_value(date(2026, 3, 2))  ->  date(2026, 3, 2)

The driver had made a bool and a datetime.date of the two columns, declared "bool" and "date", before the backend’s converters saw them. Those run all the same, and with no declared type a boolean reaches its converter as an integer: DatabaseOperations.convert_booleanfield_value makes bool of any value equal to 1 or 0, the driver’s False among them.

A decimal takes the other road. Here one is given as a Value and selected:

rate = Ship.objects.annotate(rate=models.Value(Decimal('12.50'), output_field=models.DecimalField(max_digits=5, decimal_places=2))).values_list('rate', flat=True).first()
  SQL SELECT (CAST(%s AS REAL)) AS "rate" FROM "fleet_ship" ORDER BY "fleet_ship"."name" ASC LIMIT 1 with params (Decimal('12.50'),)
  DatabaseOperations.get_decimalfield_converter(Value(Decimal('12.50')))
  converter(12.5)  (the function get_decimalfield_converter made)
    DatabaseOperations._create_decimal(12.5)  ->  Decimal('12.5')
    -> Decimal('12.5')

Value.as_sqlite had cast the parameter to "REAL", and the backend registers no converter with the driver for a decimal, so the driver returned a float. DatabaseOperations.get_decimalfield_converter makes a converter for each expression. For the column of a field with decimal places it quantizes to them in the field’s context; for anything else, as here, it makes a decimal and leaves the places as they come. Either way it starts from DatabaseOperations._create_decimal, which makes a float a decimal through a context of 15 digits, the comment’s reason being that SQLite stores only 15 significant digits and digits coming from float inaccuracy must be removed.

SQLite’s DatabaseOperations.convert_uuidfield_value makes a uuid.UUID of the 32 hexadecimal digits. A duration’s converter and a JSON value’s are the field’s. DurationField.get_db_converters adds BaseDatabaseOperations.convert_durationfield_value, which makes a datetime.timedelta of the microseconds, where the features lack BaseDatabaseFeatures.has_native_duration_field; JSONField.from_db_value passes the text to json.loads with the field’s decoder. The other backends divide the same work otherwise:

Django’s adapters map for psycopg 3 loads "jsonb" as text, the comment’s reason being to avoid a round trip through json.dumps and json.loads when a JSONField has a decoder of its own, and "inet" and "cidr" as text rather than Python’s address objects; under psycopg2 the backend does the same for "jsonb". MySQL’s django_conversions reads a TIME column with typecast_time, the comment saying that the driver returns one as a datetime.timedelta, those columns being signed and holding days, where Django expects a time.

Time zones

With USE_TZ on, Django’s datetimes are aware, and a backend whose database keeps no time zone has to store them in one. That zone is BaseDatabaseWrapper.timezone: datetime.UTC, unless the alias’s "TIME_ZONE" names a zone and then a zoneinfo.ZoneInfo of it, and None with USE_TZ off; its docstring says that a datetime read from the database is always returned in it. BaseDatabaseWrapper.timezone_name is its name, or the project’s TIME_ZONE with USE_TZ off. Both are cached on the connection. The end of the block that read Bram’s time back:

signed_on  ->  datetime(2026, 3, 14, 17, 30, tzinfo=UTC)
signed_on.tzinfo  ->  datetime.timezone.utc
timezone.localtime(signed_on)  ->  datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo'))
connection.timezone  ->  datetime.timezone.utc
connection.timezone_name  ->  'UTC'
connection.settings_dict['TIME_ZONE']  ->  None

The project’s TIME_ZONE, Oslo’s, is the default zone, the one django.utils.timezone.localtime moved the value into; the alias’s, unset, is the zone of storage. Without USE_TZ neither property reads the alias’s value, and BaseDatabaseWrapper.check_settings, the first step of BaseDatabaseWrapper.connect, refuses an alias’s "TIME_ZONE" as the connection opens, before the driver’s connection is made, here for an alias naming Oslo’s:

settings.USE_TZ = False
zoned = ConnectionHandler({'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': 'harbour.sqlite3', 'TIME_ZONE': 'Europe/Oslo'}})['default']
zoned.cursor()
...
        BaseDatabaseWrapper.connect
          BaseDatabaseWrapper.check_settings  raises ImproperlyConfigured
raises ImproperlyConfigured: Connection 'default' cannot set TIME_ZONE because USE_TZ is False.
zoned.connection is None  ->  True

A database that stores no time zone

SQLite, MySQL and Oracle keep a datetime without a time zone, in "datetime", "datetime(6)" and "TIMESTAMP" columns, and their features set BaseDatabaseFeatures.supports_timezones to false. Their adapters make an aware value naive in the connection’s zone, and their DatabaseOperations.convert_datetimefield_value makes the naive value the driver returns aware in the same zone. MySQL’s and Oracle’s DatabaseOperations.get_db_converters name it only with USE_TZ on, and SQLite’s checks the setting inside it. So on these backends the alias’s "TIME_ZONE" changes what every stored datetime means, and the documentation says it must be set where the database holds local times that another system wrote or expects to find.

PostgreSQL: the session’s time zone, and Django’s loader

PostgreSQL’s "timestamp with time zone" stores an instant, and the server writes each value back in the session’s time zone. DatabaseWrapper._configure_timezone sets the session’s zone to timezone_name where the server reports another, with the operations object’s DatabaseOperations.set_time_zone_sql. It runs as each connection opens, from DatabaseWrapper.init_connection_state or, where the alias has a pool, from the pool itself (PostgreSQL).

Under psycopg 3 each connection’s adapters map loads "timestamptz" with a subclass of BaseTzLoader that register_tzloader makes for the connection’s timezone: it loads with psycopg’s own loader and puts that zone on the value in place of the offset. With USE_TZ off the zone is None, which, the docstring says, chops the time zone, and the value is naive in the session’s zone. DatabaseWrapper.create_cursor registers the loader again on a cursor where the connection’s holds another zone; under psycopg2 it gives each cursor, with USE_TZ on, DatabaseWrapper.tzinfo_factory, which returns the connection’s zone.

BaseDatabaseWrapper.ensure_timezone, whose one caller is the test framework’s receiver of setting_changed, makes an open connection agree again after the settings change: the base class’s does nothing, and PostgreSQL’s closes the pool and runs _configure_timezone.

Time zones inside a statement

A filter on the month of a datetime, or a Trunc of one, is computed by the database in the time zone the function was given, or else the current one. Where the stored value is naive, the database has also to be told what zone it is in. SQLite’s operations object gives Django’s functions both names as parameters, the zone to compute in and the connection’s:

march = list(Sailor.objects.filter(signed_on__month=3))
  SQL SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on" FROM "crew_sailor" WHERE django_datetime_extract(%s, "crew_sailor"."signed_on", %s, %s) = %s with params ('month', 'Europe/Oslo', 'UTC', 3)
...
    _sqlite_datetime_extract(lookup_type='month', dt='2026-03-14 17:30:00', tzname='Europe/Oslo', conn_tzname='UTC')
      _sqlite_datetime_parse(dt='2026-03-14 17:30:00', tzname='Europe/Oslo', conn_tzname='UTC')  ->  datetime(2026, 3, 14, 18, 30, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo'))
      -> 3

_sqlite_datetime_parse reads the stored text with typecast_timestamp, which keeps no time zone information, gives the result the connection’s zone and moves it into the zone the SQL names, with split_tzname_delta for a name that carries an offset. MySQL’s operations object writes "CONVERT_TZ" from the connection’s zone, which, the comment in DatabaseWrapper.mysql_server_data says, returns null where UTC is not loaded into the server’s time zone table; the backend asks the server once whether UTC converts, and the features’ BaseDatabaseFeatures.has_zoneinfo_database is the answer. Oracle’s operations object names the connection’s zone in each conversion.

A truncated datetime has one more converter, after the backend’s. A Trunc of Bram’s time to the month comes back from SQLite as text, truncated in Oslo’s time, and the backend’s converter makes it aware in the connection’s zone, UTC. TruncBase.convert_value then calls BaseDatabaseOperations.convert_trunc_expression, which, for a datetime output with USE_TZ on, drops that zone and makes the value aware in the zone the truncation was computed in, the TruncBase.tzinfo it was given or, where that is None as here, the current zone:

month = Sailor.objects.annotate(month=Trunc('signed_on', 'month')).values_list('month', flat=True).get(name='Bram')
  SQL SELECT django_datetime_trunc(%s, "crew_sailor"."signed_on", %s, %s) AS "month" FROM "crew_sailor" WHERE "crew_sailor"."name" = %s LIMIT 21 with params ('month', 'Europe/Oslo', 'UTC', 'Bram')
  SQLCompiler.get_converters  ->  {0: [DatabaseOperations.convert_datetimefield_value, TruncBase.convert_value]}
...
  TruncBase.convert_value(datetime(2026, 3, 1, 0, 0, tzinfo=UTC))
    BaseDatabaseOperations.convert_trunc_expression(datetime(2026, 3, 1, 0, 0, tzinfo=UTC))
      make_aware(datetime(2026, 3, 1, 0, 0), None)  ->  datetime(2026, 3, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Oslo'))