SQLite: Datetime discrepancy due to lost timezone information

[TheTechromancer]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from pathlib import Path
from datetime import datetime
from sqlmodel import Session, SQLModel, select, create_engine, Field, DateTime, Column

class TimeTest(SQLModel, table=True):
    # force timezone awareness
    timestamp: datetime = Field(
        sa_column=Column(
            DateTime(timezone=True),
            primary_key=True
        )
    )

# create db
db_path = Path("/tmp/timetest.db")
db_path.unlink(missing_ok=True)
engine = create_engine(str(f"sqlite:///{db_path}"))
SQLModel.metadata.create_all(engine)

# instantiate obj
obj = TimeTest(**{
    "timestamp": "2024-07-12T19:55:16.980804+00:00",
})
validated = TimeTest.model_validate(obj)

# insert into db
with Session(engine, expire_on_commit=False) as session:
    session.add(validated)
    session.commit()
    # pull it back out again
    obj_new = session.exec(select(TimeTest)).all()[0]

validated_new = TimeTest.model_validate(obj_new)

for v in (validated, validated_new):
    # it lost its timezone
    print(f'{v.timestamp} / {v.timestamp.tzinfo} / {v.timestamp.timestamp()}')

# timestamps don't match
assert validated.timestamp.timestamp() == validated_new.timestamp.timestamp()

Description

In sqlite, datetime objects are off by several hours after being retrieved from the db. This is because they lose their timezone information.

This happens even when timezone awareness is forced on the field. It is specific to sqlite; postgres does not have this problem.

Operating System

Linux

SQLModel Version

0.0.19

Python Version

3.12.3

[TheTechromancer]

This is not sqlmodel's fault. It's a known bug in sqlalchemy.