From 2d4dfb959b07d3641ac5565c9f4fa57fe1267097 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 26 Aug 2006 19:46:33 +0000 Subject: [PATCH] - added "timezone=True" flag to DateTime and Time types. postgres so far will convert this to "TIME[STAMP] (WITH|WITHOUT) TIME ZONE", so that control over timezone presence is more controllable (psycopg2 returns datetimes with tzinfo's if available, which can create confusion against datetimes that dont). [ticket:275] --- CHANGES | 7 ++++- lib/sqlalchemy/databases/postgres.py | 8 ++--- lib/sqlalchemy/types.py | 12 ++++++-- test/sql/testtypes.py | 44 +++++++++++++++++++++++++++- 4 files changed, 62 insertions(+), 9 deletions(-) diff --git a/CHANGES b/CHANGES index 405610760b..3be73d3d52 100644 --- a/CHANGES +++ b/CHANGES @@ -36,7 +36,12 @@ flag for use with table reflection to help with quoting rules - unit tests updated to run without any pysqlite installed; pool test uses a mock DBAPI - urls support escaped characters in passwords [ticket:281] -- added limit/offset to UNION queries +- added limit/offset to UNION queries (though not yet in oracle) +- added "timezone=True" flag to DateTime and Time types. postgres +so far will convert this to "TIME[STAMP] (WITH|WITHOUT) TIME ZONE", +so that control over timezone presence is more controllable (psycopg2 +returns datetimes with tzinfo's if available, which can create confusion +against datetimes that dont). 0.2.7 - quoting facilities set up so that database-specific quoting can be diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 1bcf83409b..19ec95a132 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -46,7 +46,7 @@ class PGSmallInteger(sqltypes.Smallinteger): return "SMALLINT" class PG2DateTime(sqltypes.DateTime): def get_col_spec(self): - return "TIMESTAMP" + return "TIMESTAMP " + (self.timezone and "WITH" or "WITHOUT") + " TIME ZONE" class PG1DateTime(sqltypes.DateTime): def convert_bind_param(self, value, dialect): if value is not None: @@ -70,7 +70,7 @@ class PG1DateTime(sqltypes.DateTime): value.hour, value.minute, seconds, microseconds) def get_col_spec(self): - return "TIMESTAMP" + return "TIMESTAMP " + (self.timezone and "WITH" or "WITHOUT") + " TIME ZONE" class PG2Date(sqltypes.Date): def get_col_spec(self): return "DATE" @@ -89,7 +89,7 @@ class PG1Date(sqltypes.Date): return "DATE" class PG2Time(sqltypes.Time): def get_col_spec(self): - return "TIME" + return "TIME " + (self.timezone and "WITH" or "WITHOUT") + " TIME ZONE" class PG1Time(sqltypes.Time): def convert_bind_param(self, value, dialect): # TODO: perform appropriate postgres1 conversion between Python DateTime/MXDateTime @@ -102,7 +102,7 @@ class PG1Time(sqltypes.Time): # TODO: perform appropriate postgres1 conversion between Python DateTime/MXDateTime return value def get_col_spec(self): - return "TIME" + return "TIME " + (self.timezone and "WITH" or "WITHOUT") + " TIME ZONE" class PGText(sqltypes.TEXT): def get_col_spec(self): diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index 709c77e5c6..86c94eea6c 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -181,15 +181,21 @@ class Float(Numeric): class DateTime(TypeEngine): """implements a type for datetime.datetime() objects""" - pass - + def __init__(self, timezone=True): + self.timezone = timezone + def adapt(self, impltype): + return impltype(timezone=self.timezone) + class Date(TypeEngine): """implements a type for datetime.date() objects""" pass class Time(TypeEngine): """implements a type for datetime.time() objects""" - pass + def __init__(self, timezone=True): + self.timezone = timezone + def adapt(self, impltype): + return impltype(timezone=self.timezone) class Binary(TypeEngine): def __init__(self, length=None): diff --git a/test/sql/testtypes.py b/test/sql/testtypes.py index 9a25cbee50..4b4c8d47c8 100644 --- a/test/sql/testtypes.py +++ b/test/sql/testtypes.py @@ -228,7 +228,7 @@ class DateTest(AssertMixin): fnames = ['user_id', 'user_name', 'user_datetime', 'user_date', 'user_time'] - collist = [Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), Column('user_datetime', DateTime), + collist = [Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), Column('user_datetime', DateTime(timezone=False)), Column('user_date', Date), Column('user_time', Time)] if db.engine.name == 'mysql' or db.engine.name == 'mssql': @@ -273,6 +273,48 @@ class DateTest(AssertMixin): #x = db.text("select * from query_users_with_date where user_datetime=:date", bindparams=[bindparam('date', )]).execute(date=datetime.datetime(2005, 11, 10, 11, 52, 35)).fetchall() #print repr(x) +class TimezoneTest(AssertMixin): + """test timezone-aware datetimes. psycopg will return a datetime with a tzinfo attached to it, + if postgres returns it. python then will not let you compare a datetime with a tzinfo to a datetime + that doesnt have one. this test illustrates two ways to have datetime types with and without timezone + info. """ + def setUpAll(self): + global tztable, notztable, metadata + metadata = BoundMetaData(testbase.db) + + # current_timestamp() in postgres is assumed to return TIMESTAMP WITH TIMEZONE + tztable = Table('tztable', metadata, + Column("id", Integer, primary_key=True), + Column("date", DateTime(timezone=True), onupdate=func.current_timestamp()), + Column("name", String(20)), + ) + notztable = Table('notztable', metadata, + Column("id", Integer, primary_key=True), + Column("date", DateTime(timezone=False), onupdate=cast(func.current_timestamp(), DateTime(timezone=False))), + Column("name", String(20)), + ) + metadata.create_all() + def tearDownAll(self): + metadata.drop_all() + + @testbase.supported('postgres') + def testtz(self): + # get a date with a tzinfo + somedate = testbase.db.connect().scalar(func.current_timestamp().select()) + tztable.insert().execute(id=1, name='row1', date=somedate) + c = tztable.update(tztable.c.id==1).execute(name='newname') + x = c.last_updated_params() + print x['date'] == somedate + + @testbase.supported('postgres') + def testnotz(self): + # get a date without a tzinfo + somedate = datetime.datetime(2005, 10,20, 11, 52, 00) + notztable.insert().execute(id=1, name='row1', date=somedate) + c = notztable.update(tztable.c.id==1).execute(name='newname') + x = c.last_updated_params() + print x['date'] == somedate + class BooleanTest(AssertMixin): def setUpAll(self): global bool_table -- 2.47.2