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."
_______________________________________________
python-win32 mailing list
python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
