Timezone handling with postgres

[Wichert Akkerman]

Postgres can handle timezones fairly well. Using a direct select you can
see how it handles daylight saving correctly:

test=# select
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone,
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone +
interval '7 months',
'2010-08-15 12:30 Europe/Berlin'::timestamp with time zone;
timestamptz | ?column? | timestamptz
------------------------+------------------------+------------------------
2010-01-15 12:30:00+01 | 2010-08-15 12:30:00+02 | 2010-08-15 12:30:00+02
(1 row)

When using a table to store a timestamp this still works properly:

tribaspace=# create table test (moment timestamp with time zone);
CREATE TABLE
tribaspace=# insert into test values ('2010-01-15 12:30
Europe/Berlin'::timestamp with time zone);
INSERT 0 1
tribaspace=# select moment + interval '7 months' from test;
?column?
------------------------
2010-08-15 12:30:00+02
(1 row)

However that extra timezone information is lost when I use SQLAlchemy.
After adding a primary key column I use this bit of python to test the
timezone handling:

import datetime
from sqlalchemy import orm
from sqlalchemy import schema
from sqlalchemy import types

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
__tablename__ = "test"
id = schema.Column(types.Integer(), primary_key=True,
autoincrement=True)
moment = schema.Column(types.Time(timezone=True))

engine = create_engine('postgres:///test')
Base.metadata.create_all(engine)

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

row = session.query(Test).first()
print row.moment
print row.moment + datetime.timedelta(days=212)

Which outputs:

2010-01-15 12:30:00+01:00
2010-08-15 12:30:00+01:00

The second timestamp should have +02:00 as timezone due do daylight
saving differences. Unfortuantely the timezone information reported on
the column has a fixed offset instead of the more informative
Europe/Amsterdam time.

I am guessing that this is mostly due to psycopg2 not handling timezones
properly. I am wondering if SQLAlchemy itself will handle this correctly
if psycopg2 would do the right thing, and if other dialects implement
this better?

Wichert.

[Michael Bayer]

Wichert Akkerman wrote:
> Which outputs:
>
> 2010-01-15 12:30:00+01:00
> 2010-08-15 12:30:00+01:00
>
>
> The second timestamp should have +02:00 as timezone due do daylight
> saving differences. Unfortuantely the timezone information reported on
> the column has a fixed offset instead of the more informative
> Europe/Amsterdam time.
>
> I am guessing that this is mostly due to psycopg2 not handling timezones
> properly. I am wondering if SQLAlchemy itself will handle this correctly
> if psycopg2 would do the right thing, and if other dialects implement
> this better?

Its true, we don't do anything with the date objects passed to/from
psycopg2, so you'd have to ask them about best practices for handling
timezone-aware dates. Personally I don't use them, I try to store
everything as UTC across the board and deal with timezone conversions only
at the point of data collection and display.

>
> Wichert.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>