The pysqlite code in Python 2.5 has a bug. This bug also exists in the upstream pysqlite2 release, but their tracker is down so I cannot submit it there.

The bug is as follows: if you insert a datetime object into a sqlite database and then try to retrieve the object, you will (in some cases) get a datetime instance with a slightly smaller value for the microseconds field. This bug occurs because pysqlite is doing pointless floating point conversions. I describe two fixes and an extra test case below.

This bug is real. I have observed it in the wild. The test set for my application can trigger this bug about once every 20 runs.

This is what happens:

* pysqlite includes an adapter and converter function so that datetime objects can transparently be inserted and retrieved from a sqlite database column of type timestamp.

* When inserting a datetime object, pysqlite's adapter will insert the isoformat() value of the object.

* When retrieving, pysqlite will take the iso formatted string representation of the datetime object and convert it into an actual datetime object. This conversion is buggy.

* Check out line 71 of Lib/sqlite3/dbapi2.py. The code is:

microseconds = int(float("0." + timepart_full[1]) * 1000000)

And that is where the bug is. This code takes an integer value, converts it into a float (implicitly dividing by 1000000, then multiplies that by 1000000 and takes the integer part. For most values, that process gives the result you expect. For some values however, like 510241, that process gives slightly smaller values because of floating point rounding.

There are two possible fixes:

1. The simple fix is to just do rounding properly by using this line in place of the previous line:

microseconds = int(0.5 + (float("0." + timepart_full[1]) * 1000000))

This will eliminate the bug.

2. The better fix (IMHO) is to stop playing games with floating point numbers. There is absolutely no reason to introduce floats into this computation. The datetime object stores microseconds as an integer value and it gets written to the database as a stringified integer value. Taking apart that string and converting it into an integer is a lossless operation. My preferred fix is thus:

microseconds = int(timepart_full[1])

This will eliminate the bug and it has the benefit of being shorter as well.


I've attached a patch with my preferred fix as well as an extra test in the pysqlite test suite (Lib/sqlite3/test/types.py). You can run the pysqlite test suite by running Lib/sqlite3/test/types.py. Note that without my fix, the test that I added (DateTimeTests.CheckDateTimeSubSecondsFloatingPoint) will fail but with my fix it will pass.

Gerhard, can you please take a look? If not, unassign.

Fixed in upstream pysqlite. Leaving open until next merge to Python's sqlite3 module.

This has long been fixed in revision 53420. So it will be in Python 2.6
and 3.0.