Description
Migrated issue, originally created by Anonymous
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 <tom@moertel.com>
Cheers,
Tom
Attachments: sqlalchemy-sqlite-microseconds-fix.diff
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
Participants
Activity
sqlalchemy-bot commentedon Jun 27, 2008
Michael Bayer (@zzzeek) wrote:
this will be easier for you if you're starting with 0.5. For 0.4, you have to do this:
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.
4a66683 3591ef5
sqlalchemy-bot commentedon Jun 27, 2008
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jun 30, 2008
Anonymous wrote:
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 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