On Feb 25, 2013, at 10:10 AM, Gummi <gudmundur.johannes...@gmail.com> wrote:
> 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? 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). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
