CODEWITHSUNDEEP
pythonpostgresqlsqlalchemytimestamp-with-timezone
M.O.
M.O.

Reputation: 2911

SqlAlchemy use PostgreSQL timestamptz "AT TIMEZONE" in queries

I have a PostgreSQL 11 table with a column of type timestamp with timezone, and I want to display this time in various different timezones. In pure PL/pgSQL, I can write a query such as

select id, creation_date at timezone 'America/New_York' from "Test" where id=1;

which leaves all the nasty time zone handling to the database. Now, if I map the table in SqlAlchemy (using version 1.4.5):

from sqlalchemy import select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()

engine = create_engine("postgresql+psycopg2://user:pass@server/database")

class Test(Base):
    __tablename__ = "Test"
    id = Column(Integer, primary_key=True)
    creation_date = Column(DateTime(timezone=True))

I can query the the table using e.g.

with Session(engine) as session:
    test = session.scalar(select(Test).where(Test.id==1))

but this gives me test.creation_date as a Python datetime object in UTC. Of course, I can run this query and then convert the timezone in Python, but would it be possible to tell SqlAlchemy to add the at timezone 'America/New_York' part to the Test.creation_date column when selecting? EDIT: America/New_York is just an example, the actual timezone will have to be set for each query.

Upvotes: 3

Views: 3980

Answers (1)

van
van

Reputation: 76952

If you need the time zone to be this specified with every query, and the fact that according to the postgresql: Date/Time Functions and Operators

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

the solution below should work for you:

from sqlalchemy import func

expr = func.timezone('America/New_York', Test.creation_date)

test, in_ny = (
    session.query(
        Test,
        expr.label("in_ny"),
    ).filter(Test.id == 1)
).one()

Upvotes: 6

Related Questions