Are all the results (2012, 12, 10) and earlier? Maybe it's only comparing
the dates and not the times for some reason (don't know why). In that case,
since you're using strict < (rather than <=), it would make sense that no
results would be later than (2012, 12, 10).
Anthony
On Friday, August 19, 2011 2:52:59 AM UTC-4, Luis Goncalves wrote:
>
> 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.
>
