Convert timezone on Sqlalchemy get()

Question

I am messing around with timezones in a Flask application using Sqlalchemy (with Postgres). Postgres saves datetime as timestamps with time zone, and all datetimes are stored in UTC by default. Using python's Pendulum library its easy to convert to different timezones.

I have a simple sqlalchemy-based model:

class Order(Model): 
    id = Column(Integer, primary_key=True)
    code = Column(String(10))
    order_date = Column(DateTime(timezone=True))

I would like to convert the order_date of an Order - identified by an order_id - to the user's timezone upon query. I can do (I assume user.timezone is a valid timezone):

order = db.session.query(id, 
                         code,
                         func.timezone(user.timezone, Order.order_date).label('order_date'))\
          .filter(Order.id == order_id)\
          .first()

but sometimes it is easier to just get the Order:

order = Order.query.get(order_id)

In this latter way, I get the stored UTC order_date. It is possible to convert the order_date using the get function?

My workaround is to get the object, go through the model's fields (assuming that in real cases an object may have more than one datetime field) and, if a field is a datetime, convert it to the user's timezone as follows:

import datetime
import pendulum

def convert_tz(obj, tz):
    for each in obj.__dict__:
        d = getattr(obj, each)
        if isinstance(d, datetime.datetime):
            setattr(obj, each, tz.convert(d))

order = Order.query.get(order_id)
tz = pendulum.timezone(user.timezone)
convert_tz(order, tz)

It seems working but also seems too convoluted. Is there a better way? Thanks

Convert timezone on Sqlalchemy get()

0 Answers