[PATCH] Fixes SQLite database adapter to convert microseconds properly

[Former user]

Summary: When converting between Python and SQLite representations of time values, the SQLAlchemy SQLite adapter fails to convert microseconds into fractional seconds and vice versa. The enclosed patch changes two lines in sqlite.py, correcting the conversion logic (both to and from the database).

Explanation of the problem: SQLite represents time values as strings that may have a fractional-second component (e.g., a full datetime has the format 'YYYY-MM-DDTHH:MM:SS.SSS'). In Python datetime values, however, portions of a second are represented as a count of microseconds. When converting between the two time-value formats, the SQLite adapter treats the count of microseconds as if it were a fractional-second representation, skipping the necessary conversion between the two representations.

Example of the problem:

>>> import datetime
>>> import sqlalchemy.databases.sqlite
>>> dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125)  # 125 usec
>>> str(dt)
'2008-06-27 12:00:00.000125'   # correct
>>> sldt = sqlalchemy.databases.sqlite.SLDateTime()
>>> bp = sldt.bind_processor(None)
>>> bp(dt)
'2008-06-27 12:00:00.125'      # oops: 125 usec != 0.125 sec

Submitter: Tom Moertel {{{[e-mail]}}}

Cheers, Tom

[Mike Bayer]

this will be easier for you if you're starting with 0.5. For 0.4, you have to do this:

from sqlalchemy.databases.sqlite import DateTimeMixin
DateTimeMixin.__legacy_microseconds__ = False

this for the obvious reason that existing SQLite databases which contain SQLA-encoded microseconds will fail if the new behavior is switched on, without a data conversion first taking place.

4a66683c9f7853370849b78a1cb8adb6db1687f2 3591ef591f205f6563aa7f8f5c6cce261dc76d4f

[Former user]

Thanks for getting the fix in so quickly. :-)

One thing I ought to point out, however, is that my main motivation for abandoning the "legacy" representation was to avoid sorting errors in the database. For example, given the following two timestamps:

dt1 = datetime.datetime(2008, 6, 27, 12, 0, 0,      5)
dt2 = datetime.datetime(2008, 6, 27, 12, 0, 0, 275000)

dt1 is clearly < dt2, but under the legacy representation, SQLite will sort them as if the opposite were true. In short, the legacy representation makes database orderings unreliable.

You might want to mention this risk in the change log, lest people think the only reason to migrate is to achieve parity with the output of str(sometimeanddate).

Cheers, Tom

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)