Handling Timezone Column in SQLAlchemy ORM queries

[vickypalani]

Whenever I query a column representing a timestamp with timezone data, it returns a UTC datetime. Even when I update the timezone of the session with SET TIMEZONE <>, the output remains in UTC. I have a scenario in my FastAPI application where I need to dynamically change the timezone of the session based on the end user’s location. Of course, using func.timezone(desired_timezone, column) will work, but are there other possible solutions? Also, adding func.timezone() to every single query feels tiresome, as most of the queries in my application involve timezone-aware columns.

[gordthompson]

The behaviour of "timezone-aware" datetime values depends to some extent on the database being used. What database platform are you using?

[vickypalani]

Hi, I'm using PostgreSQL as my database platform.

[gordthompson]

As you probably know, PostgreSQL "timestamp with time zone" columns automatically convert and store their values as UTC

test=# select '2024-01-01 12:00:00 -0500'::timestamptz as foo;
          foo           
------------------------
 2024-01-01 17:00:00+00
(1 row)

so it is understandable why values might come back as UTC under "normal" circumstances.

However, the (database) session TIME ZONE does appear to have an effect under vanilla SQLAlchemy:

class Thing(Base):
    __tablename__ = "thing"

    id = Column(Integer, primary_key=True, autoincrement=False)
    dt = Column(DateTime(timezone=True))


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with Session(engine) as sess:
    sess.add(Thing(id=1, dt=datetime.datetime(2024, 1, 1, 12, 0)))
    sess.commit()

for tz in ["America/Toronto", "Europe/London"]:
    print(f"[{tz}]")
    with Session(engine) as sess:
        sess.execute(text(f"SET TIME ZONE '{tz}'"))
        t = sess.get(Thing, 1)
        print(t.dt)
"""
[America/Toronto]
2024-01-01 07:00:00-05:00
[Europe/London]
2024-01-01 12:00:00+00:00
"""

and engine.echo = "debug" reveals that the timestamp values are in fact returned as different "time zones" (timezone-aware datetime objects with different tzinfo= offsets)

BEGIN (implicit)
SET TIME ZONE 'America/Toronto'
[generated in 0.00049s] {}
SELECT thing.id AS thing_id, thing.dt AS thing_dt 
FROM thing 
WHERE thing.id = %(pk_1)s
[generated in 0.00066s] {'pk_1': 1}
2024-07-14 10:00:32,993 DEBUG sqlalchemy.engine.Engine Col ('thing_id', 'thing_dt')
2024-07-14 10:00:32,994 DEBUG sqlalchemy.engine.Engine Row (1, datetime.datetime(2024, 1, 1, 7, 0, tzinfo=datetime.timezone(datetime.timedelta(days=-1, seconds=68400))))
ROLLBACK
BEGIN (implicit)
SET TIME ZONE 'Europe/London'
[generated in 0.00039s] {}
SELECT thing.id AS thing_id, thing.dt AS thing_dt 
FROM thing 
WHERE thing.id = %(pk_1)s
[cached since 0.02589s ago] {'pk_1': 1}
2024-07-14 10:00:33,017 DEBUG sqlalchemy.engine.Engine Col ('thing_id', 'thing_dt')
2024-07-14 10:00:33,018 DEBUG sqlalchemy.engine.Engine Row (1, datetime.datetime(2024, 1, 1, 12, 0, tzinfo=datetime.timezone.utc))

but similar shenanigans do not appear to affect the results from FastAPI. If you want to know why that is, you will need to ask them.

[gordthompson]

I tinkered around a bit more, and I found that

async def get_async_session():
    async_session = SessionLocal()
    await async_session.execute(text("SET TIME ZONE 'America/Toronto'"))  # (new)
    try:
        yield async_session
    finally:
        await async_session.close()

does nothing with postgresql+asyncpg:// but seems to work with postgresql+psycopg://

[vickypalani]

@gordthompson Thanks for your time, indeed the issue is in asyncpg. MagicStack/asyncpg#481