Alexandre Bourget

geek joy

SQLAlchemy and Timezone support

April 27, 2009 at 12:00 PM

SQLAlchemy allows you to pass an optionnal timezone argument to the DateTime types object. However, it's only used by the PostgreSQL backend.

I was looking for a solution that would save consistently time-offset-aware datetime objects.

I found something pretty simple in the PylonsHQ pasties:

import sqlalchemy as sqla
from pytz import UTC

class UTCDateTime(sqla.types.TypeDecorator):
    impl = sqla.types.DateTime
    def convert_bind_param(self, value, engine):
        return value
    def convert_result_value(self, value, engine):
        return UTC.localize(value)

Defining this in your application allows SQLAlchemy not to depend on the pytz package, and allows you to have simple UTC-everywhere support in your database. Best of all, it's cross-database.

In the actual state, you wouldn't be able to get from the database an offset-aware datetime object. The convert_bind_param should be modified to tweak the result retrieved from the database. This way, you'd have offset-aware datetimes throughout.

Please comment if you try this.

blog comments powered by Disqus