Doing database queries on datetime.datetime fields seems broken. Here's an
example:
I have a database with a table 'transEntry' which includes a 'datetime'
field:
db.define_table('itemEntry',
* Field('dateTime','datetime'),*
Field('upc', 'string'),
Field('description', 'string'),
Field('storeNumber', 'integer'),
Field('terminalNumber', 'integer'),
Field('transactionNumber', 'integer'),
Field('operatorNumber', 'integer'),
Field('quantity', 'integer'),
Field('price', 'double'),
Field('action', 'integer'),
Field('sequenceNumber', 'integer'))
I do a search based on other fields, and it works fine:
first_search = db((db.transEntry.storeNumber==store_num) &
(db.transEntry.terminalNumber==lane_num)).select(orderby=db.transEntry.dateTime)
len(first_search)
>> 5213
Let's look at one of the results:
In [173]: ex = first_search[524].dateTime
In [174]: ex
Out[174]: datetime.datetime(2010, 12, 11, 17, 50, 55)
Now try to find a subset of the original query, entries antecedent to ex :
In [183]: broken_search = db( (db.transEntry.dateTime < ex) &
(db.transEntry.storeNumber==store_num) &
(db.transEntry.terminalNumber==lane_num) ).select(
orderby=db.transEntry.dateTime)
In [184]: len(gar2)
Out[184]: 270
?? Why are there only 270, we were expecting 523 of them??
Let's take a closer look:
In [186]: gar2[1].dateTime
Out[186]: datetime.datetime(2010, 12, 10, 10, 55, 39)
In [187]: gar2[2].dateTime
Out[187]: datetime.datetime(2010, 12, 10, 10, 56, 19)
In [189]: gar2[269].dateTime
*Out[189]: datetime.datetime(2010, 12, 10, 22, 40, 26)*
In [190]: ex
Out[190]: datetime.datetime(*2010, 12, 11, 17, 50, 55*)
?? For some reason, the closest result found is almost 24 hours away from
'ex' ??
*?? Why didn't it find this one :*
In [191]: *gar[523].dateTime*
Out[191]: *datetime.datetime(2010, 12, 11, 17, 49, 37)*
I can't understand what's happening! It seems that comparisons of datetime
fields in db queries just don't work correctly?
If anyone can explain/fix this, it will be much appreciated!!
Thanks!
Luis.
