SQLAlchemy losing timezone information with SQLite

Question

I'm having an issue where SQLAlchemy (versions 0.6.4 & 0.6.8) is losing the timezone information from a datetime object when committed to an SQLite database with the column defined with SQLAlchemy's DateTime class. (I realise that SQLAlchemy is converting this to a string and back, which is what I believe to be half the problem).

As code speaks louder than words, I have the following example/reproducer:

import datetime, pytz, sqlalchemy
from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

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

class Example(Base):
    __tablename__ = "example"
    id = Column(Integer, primary_key=True)
    date = Column(DateTime(timezone=True))

    def __init__(self, date):
        self.date = date

Base.metadata.create_all(engine)

aucklandtz = pytz.timezone('Pacific/Auckland')
exdatetime = datetime.datetime(2011,8,8,2,23)

print aucklandtz, exdatetime

# inject TZ to exdatetime:
injdatetime = aucklandtz.localize(exdatetime)

print injdatetime

newrecord = Example(injdatetime)
print newrecord.date
session.add(newrecord)
session.commit()
print newrecord.date

From the console output, the main thing is that it is inserting the record into SQLite as the value '2011-08-08 02:23:00.000000'.

Is there an easy way to fix this, or do I need to add the TZ information to a separate column/store everything as timestamps and juggle between the formats?

Answer 1

Python's strptime methods don't support the %z formatting directive, so there would be no way to get the timezone back out of the string in the database. I assume this is why it isn't stored.

You have two options. You already mentioned one -- store the timezone in another column.

The generally recommended solution to this problem, however, is different. Instead of storing a timezone, either with the datetime in Python or in the database, convert all datetimes to UTC immediately on receiving them from the user, and only ever deal with UTC internally. Later, when you have to display the result back to the user, convert it to the proper timezone.

This separates the actual time from something which you can't depend on being the same. What if the user of your program moves from Auckland to Japan, or the government of New Zealand changes their timezones?

Answer 2

There was an excellent blog post by Armin Ronacher titled "“Eppur si muove!”* – Dealing with Timezones in Python". It basically says following:

1. Internally always use offset naive datetime objects and consider them UTC.
2. When interfacing with the user, convert to and from local time.