How to store timezone aware datetime values in SqlAlchemy / Mysql?

Question

I want to store a Python timezone aware datetime attribute in a mysql database. I use SqlAlchemy. In the SqlAlchemy class definition I specify the column in the following way:

    a_utcDatetime = Column(DATETIME(fsp=6))

I realize that in the database that column has no timezone information anymore. Additionally after reading from the database, the variable has lost its timezone information, too.

Is there any way how I can configure a_utcDatetime as timezone aware already at definition time?

Answer 1

If you really want to store the timezone offset (and not just follow the convention of storing all date/time values as UTC) then you'll need a separate column for that offset value. I just tried this with MySQL and it seems to work:

from datetime import datetime, timezone, timedelta

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    text,
    __version__ as sa_version,
    DATETIME,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

connection_uri = "mysql+pymysql://root:toot@localhost:3307/mydb"
engine = create_engine(connection_uri, echo=False)
Base = declarative_base()

with engine.begin() as conn:
    conn.execute(text("DROP TABLE IF EXISTS event_log"))


class EventLog(Base):
    __tablename__ = "event_log"
    id = Column(Integer, primary_key=True)
    event = Column(String(50))
    local_datetime = Column(DATETIME)
    tz_offset_minutes = Column(Integer)

    @property
    def dt_aware(self):
        return self.local_datetime.replace(
            tzinfo=timezone(timedelta(minutes=self.tz_offset_minutes))
        )

    @dt_aware.setter
    def dt_aware(self, value):
        self.local_datetime = value.replace(tzinfo=None)
        self.tz_offset_minutes = (
            value.tzinfo.utcoffset(value).total_seconds() // 60
        )

    def __repr__(self):
        return (
            f"<EventLog(id={self.id}, "
            f"event={repr(self.event)}, "
            f"str(dt_aware)='{self.dt_aware}'>"
        )


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

my_event_start = datetime(
    2020, 4, 20, 16, 20, 0, tzinfo=timezone(timedelta(hours=-7))
)
my_event = EventLog(id=1, event="an event", dt_aware=my_event_start)

session.add(my_event)
session.commit()

# check results by querying database directly
sql = text("SELECT * FROM event_log ORDER BY id")
with engine.begin() as conn:
    result = conn.execute(sql).fetchall()
print(result)
""" console output:
[(1, 'an event', datetime.datetime(2020, 4, 20, 16, 20), -420)]
"""

print(my_event)
""" console output:
<EventLog(id=1, event='an event', str(dt_aware)='2020-04-20 16:20:00-07:00'>
"""

print(type(my_event.dt_aware))  # <class 'datetime.datetime'>