Re: [sqlalchemy] Timezone handling with postgres
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 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. > > -- 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.
[sqlalchemy] Timezone handling with postgres
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.
