Alexandre Bourget

geek joy

Archives for April 2009

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.

Read and Post Comments

Python timezone sweetness

April 23, 2009 at 12:00 PM

I was looking for Timezone support in Python, especially with SQLAlchemy.

I found several things that could help me implement timezone support in my application.

First, I found the pytz package, which is simply awsome, and deal with all your timezone needs

Here is an example usage of the pytz package. Here we load the necessary stuff:

import datetime
import pytz

Then we'll need to get my timezone, which is GMT-0500 but has all sorts of Daylight saving weirdness. Happily, pytz deal with all this itself. Let's also create a normal datetime object.

>>> mytz = pytz.timezone("America/Montreal")
>>> mytz
<DstTzInfo 'America/Montreal' EST-1 day, 19:00:00 STD>
>>> mydt =
>>> mydt
datetime.datetime(2009, 4, 22, 20, 0)

First operation, let's create a timezone-aware datetime object from this one. With fromutc, we'll assume my naive datetime was actually UTC time (at GMT+0000)

>>> newdt = mytz.fromutc(mydt)
>>> newdt
datetime.datetime(2009, 4, 22, 16, 0, tzinfo=<DstTzInfo 'America/Montreal' EDT-1 day, 20:00:00 DST>)

What if you have a random naive datetime object, and you'd like to attach a timezone to it to make it offset-aware ? This requires you to know to which timezone it belongs.

>>> this_time = datetime.datetime(2009, 4, 25, 12, 30, 0)
>>> vanc_tz = pytz.timezone('America/Vancouver')
>>> vanc_time = vanc_tz.localize(this_time)

Now what if you want to know the time in another timezone, but you only have the time in the vanc_tz timezone ?

>>> mtl_tz = pytz.timezone('America/Montreal')
>>> mtl_time = vanc_time.astimezone(mtl_tz)
>>> mtl_time
datetime.datetime(2009, 4, 25, 15, 30, tzinfo=<DstTzInfo 'America/Montreal' EDT-1 day, 20:00:00 DST>)

Noticed the switch from 12 to 15 ?

You can also get the current time for a certain timezone:

>>> vanc_now =
>>> vanc_now
datetime.datetime(2009, 4, 22, 18, 0, 34, 117582, tzinfo=<DstTzInfo 'America/Vancouver' PDT-1 day, 17:00:00 DST>)

or at UTC:

>>> utc_now = datetime.datetime.utcnow()

Isn't that sweet ?

Read and Post Comments