Commits

Mike Bayer committed 8686b46

- 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]

  • Participants
  • Parent commits e031488

Comments (0)

Files changed (4)

 - 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

lib/sqlalchemy/databases/postgres.py

         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:
                                  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"
         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
         # 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):

lib/sqlalchemy/types.py

 
 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):

test/sql/testtypes.py

 
         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':
         #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