TypeError with Datetimes in SQLModel

February 29, 2024

I’m working with SQLModel ORM framework in a Python application and encountering a TypeError related to datetime objects when trying to insert new records into a PostgreSQL database.

from datetime import datetime, timezone
import uuid
from sqlmodel import Field, SQLModel, Relationship, UniqueConstraint
from typing import List

class UserBase(SQLModel):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    phone_number: str = Field(max_length=255)
    phone_prefix: str = Field(max_length=10)

class User(UserBase, table=True):
    __table_args__ = (
        UniqueConstraint("phone_number", "phone_prefix", name="phone_numbe_phone_prefix_constraint"),
    )
    registered_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    interests: List["Interest"] = Relationship(back_populates="user")

When attempting to insert a new User record, the following error is encountered:

E   TypeError: can't subtract offset-naive and offset-aware datetimes

asyncpg/pgproto/./codecs/datetime.pyx:152: TypeError

The above exception was the direct cause of the following exception:

self = <sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_cursor object at 0x108b78ee0>
operation = 'INSERT INTO "user" (id, phone_number, phone_prefix, registered_at) VALUES ($1::UUID, $2::VARCHAR, $3::VARCHAR, $4::TIMESTAMP WITHOUT TIME ZONE)'
parameters = ('d9999373-a43d-4154-935c-f28f13f17d3e', '8545227945', '+342', datetime.datetime(2024, 2, 29, 18, 25, 54, 21935, tzinfo=datetime.timezone.utc))

How can I resolve this TypeError to ensure compatibility between the timezone-aware datetime objects in my SQLModel?

>Solution :

The problem is that by default any datetimes in sqlmodel are timezone-unaware:

    registered_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))

So when inserting a timezone-aware datetime, like datetime.now(timezone.utc), you run into issues. The solution is to declare the field to be timezone-aware (untested, based on this Github issue):

import sqalchemy as sa

...

    registered_at: datetime = Field(
        # Add this parameter
        sa_column=sa.Column(sa.DateTime(timezone=True), nullable=False),
        default_factory=lambda: datetime.now(timezone.utc)
    )