I think I have figured it out, with the help of a python expert at work. I am using sqlite3, which internally stores data as strings.
When creating my real database (not the toy example), I read raw date and time data as an isoformat string, and relied on DAL/web2py to convert. But perhaps in sqlite the field was just stored as the isoformat string: db.recs.insert( ...., dateTime = isoformat_string, ... ) where 'dateTime' is a field of type 'datetime' If I look at a database row, dateTime is shown as a datetime object, and includes both the proper date and time, so presumably in reading the sqlite database web2py/DAL knows how to translate an isoformat to a datetime: In [12]: db.recs[110].dateTime Out[12]: datetime.datetime(2010, 12, 11, 16, 16, 36) The mismatch occurs when attempting to do a db query with datetime fields, because I think that web2py/DAL converts a datetime to a string with str() instead of datetime.isoformat(), and so when sqlite does the comparision, only the date matches, but not the time: In [7]: str(dt) Out[7]: '2010-12-11 16:34:34' In [8]: dt.isoformat() Out[8]: '2010-12-11T16:34:34' Before I had this all figured out, I had added a new field to my database, iso_str, of type 'string', and now I do all my date/time queries with that and it works fine. Presumably if I rebuilt my database so that I converted my isoformat date/time data to a datetime object before inserting into the DB, all would be fine too! Luis.
