[python-win32] Re: ODBC date and time issues
Roger Upole
rwupole at msn.com
Mon Oct 6 05:06:18 CEST 2008
Joe Healy wrote:
>
> Hi,
>
> We've been having some problems with the dbi odbc interface to
> databases. The problems have been occurring when using MS SQL server or
> MS Access databases querying datetimes over daylight savings
> boundaries.
>
> The following table containing both string representations and
> datetimes demonstrates our problem:
>
> ID, Date String, DBI String, DBI long, Count
> 0, 2008-10-05 00:30:00, Sun Oct 05 00:30:00 2008, 1223130600, 0.000
> 1, 2008-10-05 00:40:00, Sun Oct 05 00:40:00 2008, 1223131200, 1.000
> 2, 2008-10-05 00:50:00, Sun Oct 05 00:50:00 2008, 1223131800, 2.000
> 3, 2008-10-05 01:00:00, Sun Oct 05 01:00:00 2008, 1223132400, 3.000
> 4, 2008-10-05 01:10:00, Sun Oct 05 01:10:00 2008, 1223133000, 4.000
> 5, 2008-10-05 01:20:00, Sun Oct 05 01:20:00 2008, 1223133600, 5.000
> 6, 2008-10-05 01:30:00, Sun Oct 05 01:30:00 2008, 1223134200, 6.000
> 7, 2008-10-05 01:40:00, Sun Oct 05 01:40:00 2008, 1223134800, 7.000
> 8, 2008-10-05 01:50:00, Sun Oct 05 01:50:00 2008, 1223135400, 8.000
> 9, 2008-10-05 02:00:00, Sun Oct 05 01:00:00 2008, 1223132400, 9.000
> 10, 2008-10-05 02:10:00, Sun Oct 05 01:10:00 2008, 1223133000, 10.000
> 11, 2008-10-05 02:20:00, Sun Oct 05 01:20:00 2008, 1223133600, 11.000
> 12, 2008-10-05 02:30:00, Sun Oct 05 01:30:00 2008, 1223134200, 12.000
> 13, 2008-10-05 02:40:00, Sun Oct 05 01:40:00 2008, 1223134800, 13.000
> 14, 2008-10-05 02:50:00, Sun Oct 05 01:50:00 2008, 1223135400, 14.000
> 15, 2008-10-05 03:00:00, Sun Oct 05 03:00:00 2008, 1223136000, 15.000
> 16, 2008-10-05 03:10:00, Sun Oct 05 03:10:00 2008, 1223136600, 16.000
> 17, 2008-10-05 03:20:00, Sun Oct 05 03:20:00 2008, 1223137200, 17.000
>
> The first column is a row id, the second is the string version of the
> date, the third is the datetime column converted to a string and the
> fourth is the datetime column converted to a long.
>
> At row 10 (02:00:00), we see 01:00:00 for the second time. Then at row
> 16, we then see 03:00:00 onwards.
>
> So it appears that the datetimes are being converted to daylight
> savings only between the hours of 2 and 3.
>
> The code to create the database and generate this table is attached.
>
> I've also looked at the code for creating these dbiDate objects which
> is in dateCopy in odbc.cpp and a guess as to the problem is different
> assumptions about daylight savings from various parts of the C
> library. The setting of gt.tm_isdst to -1 appears to be the problem to
> me and I feel (at least for our purposes) that a value of 0 might be
> more appropriate.
>
> Looking at this, it appears that the data we require is available in
> the dt object:
>
> static PyObject *dateCopy(const void *v, int sz)
> {
> const TIMESTAMP_STRUCT *dt = (const TIMESTAMP_STRUCT *) v;
> struct tm gt;
> gt.tm_isdst = -1; /* figure out DST */
> gt.tm_year = dt->year-1900;
> gt.tm_mon = dt->month-1;
> gt.tm_mday = dt->day;
> gt.tm_hour = dt->hour;
> gt.tm_min = dt->minute;
> gt.tm_sec = dt->second;
> return dbiMakeDate(PyInt_FromLong(mktime(>)));
> }
>
> Is it relatively easy to modify or add code to this library to make it
> return python datetime objects or something with direct access to the
> original structs values?
>
> If I (or someone else) was to do this, would it be likely to be
> accepted into the main python-win32 library?
>
> For our purposes, we are after a naive handling of the datetimes as we
> need to ensure our calculations are correct across timezone
> boundaries. Conversion to values in a particular timezone is handled
> at display time.
>
> For reference, we have tested this using python 2.3 and 2.5, builds
> 203, 211 and 212 of python-win32, with New Zealand and Australian time
> zone changes for Sept/Oct 2008 on Vista, XP and Server 2003.
>
> As an aside, the testDates method in test_odbc.py looks a little bit
> strange. Is it actually testing against the string 'd' instead of the
> variable d?
>
> Thanks in advance,
>
> Joe
>
>
--------------------------------------------------------------------------------
>
> import os
> import tempfile
> import pythoncom
> from win32com.client.gencache import EnsureDispatch
> from win32com.client import constants
>
> import dbi, odbc
> from datetime import datetime, timedelta
>
> # DB creation copied from test_odbc.py from win32
>
> db_filename = os.path.join(tempfile.gettempdir(), "test_odbc.mdb")
> if os.path.isfile(db_filename):
> os.unlink(db_filename)
>
> # Create a brand-new database - what is the story with these?
> for suffix in (".36", ".35", ".30"):
> try:
> dbe = EnsureDispatch("DAO.DBEngine" + suffix)
> break
> except pythoncom.com_error:
> pass
> else:
> raise RuntimeError, "Can't find a DB engine"
>
> workspace = dbe.Workspaces(0)
>
> newdb = workspace.CreateDatabase(db_filename,
> constants.dbLangGeneral,
> constants.dbEncrypt)
>
> newdb.Close()
>
>
>
>
> dbConnStr = "Driver={Microsoft Access Driver (*.mdb)};dbq=%s;Uid=;Pwd=;" \
> % (db_filename,)
> print dbConnStr
>
> dbConn = odbc.odbc(dbConnStr)
> dbCur = dbConn.cursor()
>
> createFcTableQuery = ["""create table ODBC_DST_Test (
> ID integer,
> DateTime_DBI date,
> DateTime_STR varchar(50),
> Data float
> )"""]
>
> print
> print "Creating DB in %s..." %dbConnStr
>
> for q in createFcTableQuery:
> print q
> dbCur.execute(q)
>
>
> # Test commit behaviour
> sqlInsertPacket = "INSERT INTO ODBC_DST_Test (ID, DateTime_DBI,
> DateTime_STR, Data) \
> values (%d, '%s', '%s', %d)"
>
>
> print
> print "Inserting data..."
> time = datetime(2008, 10, 5, 0, 30, 0)
> endTime = datetime(2008, 10, 5, 3, 30, 0)
> i = 0
> while time < endTime:
>
> thisSqlInsertPacket = sqlInsertPacket % (i, time, time, i)
> print thisSqlInsertPacket
> dbCur.execute(thisSqlInsertPacket)
>
> time += timedelta(minutes=10)
> i += 1
>
>
> print
> print " ID, Date String, DBI String, DBI long,
> Count"
>
> # Test data dates
> dbCur.execute("SELECT * from ODBC_DST_Test")
> for r in dbCur.fetchall():
> id, dbidate, strdate, data = r
> print "%3d, %s, %s, %d, %7.3f" %(id, strdate, str(dbidate),
> long(dbidate), data)
>
>
> dbCur.close()
> dbConn.close()
>
> print
> print "Done."
Coincidentally, the odbc module has recently been changed to use datetime
objects,
along with a number of other improvements. (test_odbc.py has been fixed,
too)
The dbi module is going away completely.
If you can build from source, try using the code from CVS. Any help testing
is
also appreciated.
Roger
More information about the python-win32
mailing list