Fixing timezone-aware datetime errors with asyncpg

Store instants in TIMESTAMP WITH TIME ZONE columns — DateTime(timezone=True), or type_annotation_map={datetime: DateTime(timezone=True)} on your base — and only ever pass aware datetimes such as datetime.now(UTC); asyncpg refuses aware values for plain timestamp columns and silently misreads naive values for timestamptz. This guide belongs to dialect-specific gotchas and driver quirks.

Quick Answer

The error comes from asyncpg's binary encoder, not from PostgreSQL, and it appears the first time an aware datetime meets a column declared without a time zone.

Before — Mapped[datetime] defaults to a naive column, the code uses aware values:

import datetime as dt

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    placed_at: Mapped[dt.datetime]   # TIMESTAMP WITHOUT TIME ZONE


order = Order(placed_at=dt.datetime.now(dt.UTC))
session.add(order)
await session.commit()
# sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error)
# <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1:
# datetime.datetime(2026, 9, 17, 9, 0, tzinfo=datetime.timezone.utc)
# (can't subtract offset-naive and offset-aware datetimes)

After — timezone-aware columns everywhere, aware values everywhere:

import datetime as dt

from sqlalchemy import DateTime, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    type_annotation_map = {dt.datetime: DateTime(timezone=True)}


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    placed_at: Mapped[dt.datetime]                                   # TIMESTAMPTZ
    created_at: Mapped[dt.datetime] = mapped_column(server_default=func.now())


order = Order(placed_at=dt.datetime.now(dt.UTC))
session.add(order)
await session.commit()          # works; placed_at reads back as an aware UTC datetime

dt.UTC is Python 3.11's alias for dt.timezone.utc. datetime.utcnow(), which returns a naive value despite its name, is deprecated since Python 3.12 and is the most common source of naive datetimes in older code.

Execution Context & Async Workflow Integration

PostgreSQL has two timestamp types with very different meanings. timestamp without time zone stores a wall-clock reading — "09:00 on 17 September" — with no idea where on Earth it was read. timestamp with time zone stores an instant, internally in UTC, and converts on input and output. Neither stores the original zone.

asyncpg talks to PostgreSQL in the binary protocol, which means it encodes each value on the client rather than sending text for the server to parse. For a timestamp column, its encoder computes the offset from the PostgreSQL epoch by subtracting a naive epoch datetime from your value. An aware value cannot be subtracted from a naive one in Python, which is exactly the message in the error: can't subtract offset-naive and offset-aware datetimes. The driver is refusing to guess which wall-clock reading you meant.

For a timestamptz column, the encoder converts your value to UTC with astimezone(). For an aware value that is exact. For a naive value, Python's astimezone() assumes the naive datetime is in the local time zone of the process — so no error is raised, and the stored instant depends on the TZ environment variable of whatever container ran the code. In a UTC container the result looks right; on a developer laptop in Berlin, or in a container image whose zone was changed, it is off by hours, silently.

psycopg behaves differently because it sends timestamps as text: a naive value arrives without an offset and the server interprets it using the connection's TimeZone setting. A service that moves from psycopg to asyncpg can therefore shift the meaning of naive datetimes without changing a line of model code — one of the reasons migrating from psycopg2 to asyncpg calls for an audit of datetime handling.

SQLAlchemy sits between these layers and passes values through. DateTime() is timezone=False and emits TIMESTAMP WITHOUT TIME ZONE; DateTime(timezone=True) emits TIMESTAMP WITH TIME ZONE. A bare Mapped[datetime] annotation resolves to DateTime() unless the base's type_annotation_map says otherwise, which is why the one-line map in the quick answer fixes an entire codebase's columns — the mechanism is covered in using mapped_column instead of Column.

Resolving Warnings, Errors & Common Mistakes

A guard at the type level turns the silent failure into a loud one. A small TypeDecorator that rejects naive datetimes catches every code path that would otherwise write a host-dependent instant:

import datetime as dt

from sqlalchemy import DateTime
from sqlalchemy.types import TypeDecorator


class AwareDateTime(TypeDecorator):
    impl = DateTime(timezone=True)
    cache_ok = True

    def process_bind_param(self, value: dt.datetime | None, dialect):
        if value is not None and value.tzinfo is None:
            raise ValueError(f"naive datetime {value!r}: pass an aware value, e.g. datetime.now(UTC)")
        return value

Put it in the type_annotation_map instead of plain DateTime(timezone=True) while a codebase is being cleaned up, and remove it once the errors stop — or keep it, since the check costs a single attribute lookup per value.

Advanced: Migrating Columns to timestamptz

Fixing the model is the easy half. Existing timestamp columns hold naive values, and converting them requires deciding what zone those values were written in — usually UTC, if the application used datetime.utcnow(), but not always.

The conversion states the zone explicitly with USING:

# alembic/versions/4e8b_orders_timestamptz.py
import sqlalchemy as sa
from alembic import op


def upgrade() -> None:
    # PostgreSQL 12+: with TimeZone = UTC this conversion does not rewrite the table.
    op.execute("SET LOCAL TimeZone = 'UTC'")
    op.alter_column(
        "orders", "placed_at",
        existing_type=sa.DateTime(timezone=False),
        type_=sa.DateTime(timezone=True),
        postgresql_using="placed_at AT TIME ZONE 'UTC'",
        existing_nullable=False,
    )


def downgrade() -> None:
    op.execute("SET LOCAL TimeZone = 'UTC'")
    op.alter_column(
        "orders", "placed_at",
        existing_type=sa.DateTime(timezone=True),
        type_=sa.DateTime(timezone=False),
        postgresql_using="placed_at AT TIME ZONE 'UTC'",
        existing_nullable=False,
    )

placed_at AT TIME ZONE 'UTC' applied to a naive timestamp means "this wall-clock reading was in UTC; give me the instant", which is what the conversion needs. Since PostgreSQL 12, a timestamp to timestamptz change is recognised as not requiring a table rewrite when the session TimeZone is UTC, so the migration is a brief catalog change even on large tables — the SET LOCAL makes sure that condition holds regardless of the database's default. Verify on a copy first with \timing and table size before and after; if your server's version or settings force a rewrite, fall back to the expand-and-contract approach in renaming a column without downtime.

Deploy order matters more than usual here. Once the column is timestamptz, any still-running old code that writes naive datetimes will not fail — asyncpg will silently interpret them in the host zone. If every host runs in UTC, that is harmless. If any does not, set TZ=UTC on every host before the migration runs, then ship the model change and the switch to aware datetimes in the release that follows it; aware values cannot be written to the old timestamp column, so the application change cannot go first.

After the migration, check the results are what you expect with a spot query that shows the stored instant in a zone you know: SELECT placed_at AT TIME ZONE 'Europe/Berlin' FROM orders ORDER BY id DESC LIMIT 5. A row that was placed at 10:00 Berlin time should read 10:00, not 11:00 or 08:00.

Working With Aware Datetimes in Queries and Reports

Once columns and values are aware, a few query patterns need a second look, because "day", "week" and "month" are wall-clock concepts and a timestamptz column stores instants.

Date bucketing needs a zone. func.date_trunc("day", Order.placed_at) truncates in the session time zone, so the same query returns different buckets on connections configured differently. Name the zone in the query instead:

import datetime as dt

from sqlalchemy import func, select

from shop.models import Order


def daily_revenue(zone: str, since: dt.datetime):
    local_day = func.date_trunc("day", func.timezone(zone, Order.placed_at)).label("day")
    return (
        select(local_day, func.sum(Order.total_cents).label("revenue_cents"))
        .where(Order.placed_at >= since)
        .group_by(local_day)
        .order_by(local_day)
    )


stmt = daily_revenue("Europe/Berlin", dt.datetime(2026, 9, 1, tzinfo=dt.UTC))

timezone(zone, timestamptz) converts an instant to that zone's wall clock, so the truncation happens on Berlin days regardless of the connection's settings.

Parameters should be aware too. where(Order.placed_at >= since) with an aware since is an instant comparison and uses an index on placed_at directly. Converting the column instead — where(func.timezone(zone, Order.placed_at) >= local_start) — defeats that index. Convert the boundary in Python with zoneinfo, keep the column bare.

Set the session zone deliberately. asyncpg returns timestamptz values as aware datetimes in UTC regardless of the session zone, so application code is unaffected by it. SQL functions like date_trunc and now()::date are affected, and a pool of connections with inconsistent settings produces inconsistent reports. Pin it on the engine: connect_args={"server_settings": {"timezone": "UTC"}}.

Keep display conversion at the edge. Store and compute in UTC, convert to a user's zone only when rendering — value.astimezone(ZoneInfo(user.timezone)). That keeps every comparison in the database and in Python an instant-to-instant comparison, which is the property that makes the errors in this guide impossible. The broader engine settings that belong alongside the time zone are covered in setting up an async engine from scratch.

Frequently Asked Questions

Related

• Dialect-Specific Gotchas and Driver Quirks — The parent guide: behaviour that differs between drivers and databases.
• Handling asyncpg prepared statement errors with PgBouncer — Another asyncpg-specific failure and its fix.
• Migrating from psycopg2 to asyncpg — The other differences to audit when changing drivers.
• Loading rows with Postgres COPY through asyncpg — Where binary encoding makes type correctness mandatory.