Time zone and daylight savings converted by the database instead of sqlalchemy

[Gummi]

Hi,

My database has all datetime values as UTC.
Now I want to map the values for various time zones (depending on customers) and take daylight savings into an account.

Here is a valid example in Postgres.  It queries the UTC based database for values after 02:00 Danish time

set time zone 'Europe/Copenhagen';
select ts, ts at time zone 'Europe/Copenhagen'  from sample_ts
where ts >= '2013-03-31 02:00:00' at time zone 'UTC';

The 1st column is "ts" (in UTC), 2nd column is "ts" at Europe/Copenhagen so the database maps the values to the correct time zone.

I have been looking for how to do this in sqlalchemy without luck.  It seems that this conversion must be done in Python even though the database can do this in a perfect way.

Has someone found a way to "allow" the database to handle this?

Thanks,
   - Gummi

[Gummi]

By the way, my database table is using column type "timestamp" without time zone (only storing in UTC).

[Michael Bayer]

That separate "set time zone" step is a bit awkward, so assuming that's necessary for every tz-dependent operation, I'd likely create a Postgresql function that performs the comparison. Then you can call it just by select([func.name_of_my_function(x, y)]), and additionally you can call it multiple times within a single statement.

that said, doing the conversions in Python and only exposing the database to pure UTC values is very simple using the tz module and I'm not seeing what the disadvantage to that approach is. It's how I do it, among other things it allows the code to be database agnostic (we run the same app against Postgresql and MS SQL Server here).