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