classification
Title: sqlite3 microseconds
Type: behavior Stage: resolved
Components: Library (Lib) Versions: Python 3.4, Python 3.2, Python 3.3, Python 2.7
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: Nosy List: frankmillman, ghaering, petri.lehtinen, pitrou, python-dev, serhiy.storchaka, thiderman
Priority: normal Keywords: patch

Created on 2012-05-04 06:31 by frankmillman, last changed 2013-02-26 19:48 by petri.lehtinen. This issue is now closed.

Files
File name Uploaded Description Edit
issue14720.diff thiderman, 2013-02-23 17:34 review
Messages (11)
msg159905 - (view) Author: Frank Millman (frankmillman) Date: 2012-05-04 06:31
sqlite3/dbapi2.py contains the following - 

    def convert_timestamp(val): 
        datepart, timepart = val.split(b" ")
        timepart_full = timepart.split(b".")
        [...] 
        if len(timepart_full) == 2: 
            microseconds = int(timepart_full[1]) 
        else: 
            microseconds = 0 

It assumes that 'timepart_full[1]' is a string containing 6 digits. 

I have a situation where the string containing 3 digits, so it gives the wrong result. For example, if the string is '456', this represents 456000 microseconds, but sqlite3 returns 456 microseconds.

I think that it should right-zero-fill the string to 6 digits before converting to an int, like this - 

    microseconds = int('{:0<6}'.format(timepart_full[1]))
msg159941 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2012-05-04 15:46
Can be reproduced with:

>>> con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
>>> cur = con.cursor()
>>> cur.execute("CREATE TABLE t (x TIMESTAMP)")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.execute("SELECT * FROM t")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.fetchall()
[(datetime.datetime(2012, 4, 4, 15, 6, 0, 456),)]
msg182119 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2013-02-14 20:17
Can convert_timestamp(val) be implemented as datetime.datetime.strptime(val.decode(), '%Y-%m-%d %H:%M:%S.%f')?
msg182776 - (view) Author: Lowe Thiderman (thiderman) * Date: 2013-02-23 17:31
> Can convert_timestamp(val) be implemented as datetime.datetime.strptime(val.decode(), '%Y-%m-%d %H:%M:%S.%f')?

No. The microseconds are not always included, as can be seen by the current implementation. Nice idea though!
msg182777 - (view) Author: Lowe Thiderman (thiderman) * Date: 2013-02-23 17:34
Add patch for 2.7 branch with regression test.
msg182786 - (view) Author: Roundup Robot (python-dev) Date: 2013-02-23 18:12
New changeset 6911df35b7b6 by Petri Lehtinen in branch '2.7':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/6911df35b7b6

New changeset 46d5317a51fb by Petri Lehtinen in branch '3.2':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/46d5317a51fb

New changeset 46c96693296f by Petri Lehtinen in branch '3.3':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/46c96693296f

New changeset 6342055ac220 by Petri Lehtinen in branch 'default':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/6342055ac220
msg182787 - (view) Author: Petri Lehtinen (petri.lehtinen) * (Python committer) Date: 2013-02-23 18:13
Applied, thanks!
msg182856 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2013-02-24 08:22
convert_timestamp() can silently return wrong result if seconds saved with more than millisecond precision (i.e. '2012-04-04 15:06:00.000123456'). I propose or truncate fractional part to 6 digits ('{:0<6.6}') or explicitly raise an exception if len(timepart_full[1]) > 6.
msg182858 - (view) Author: Petri Lehtinen (petri.lehtinen) * (Python committer) Date: 2013-02-24 08:33
Serhiy Storchaka wrote:
> convert_timestamp() can silently return wrong result if seconds
> saved with more than millisecond precision (i.e. '2012-04-04
> 15:06:00.000123456'). I propose or truncate fractional part to 6
> digits ('{:0<6.6}') or explicitly raise an exception if
> len(timepart_full[1]) > 6.

That's a good point. Also, '2012-04-04 15:06:00.1234567' fails with a
ValueError when executing the SELECT statement, because the
microsecond part is not in range 0-999999.

Truncating the fractional part to 6 characters sounds good to me,
because that way we get the best possible precision without failing
abruptly.
msg183073 - (view) Author: Roundup Robot (python-dev) Date: 2013-02-26 19:47
New changeset eb45fd74db34 by Petri Lehtinen in branch '2.7':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/eb45fd74db34

New changeset ae25a38e6c17 by Petri Lehtinen in branch '3.2':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/ae25a38e6c17

New changeset 17673a8c7083 by Petri Lehtinen in branch '3.3':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/17673a8c7083

New changeset 0db66afbd746 by Petri Lehtinen in branch 'default':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/0db66afbd746
msg183075 - (view) Author: Petri Lehtinen (petri.lehtinen) * (Python committer) Date: 2013-02-26 19:48
Fixed.
History
Date User Action Args
2013-02-26 19:48:40petri.lehtinensetmessages: + msg183075
2013-02-26 19:47:30python-devsetmessages: + msg183073
2013-02-24 08:33:13petri.lehtinensetmessages: + msg182858
2013-02-24 08:22:44serhiy.storchakasetmessages: + msg182856
2013-02-23 18:13:51petri.lehtinensetstatus: open -> closed

versions: + Python 3.4
nosy: + petri.lehtinen

messages: + msg182787
resolution: fixed
stage: resolved
2013-02-23 18:12:44python-devsetnosy: + python-dev
messages: + msg182786
2013-02-23 17:34:20thidermansetfiles: + issue14720.diff
keywords: + patch
messages: + msg182777
2013-02-23 17:31:56thidermansetnosy: + thiderman
messages: + msg182776
2013-02-14 20:17:17serhiy.storchakasetnosy: + serhiy.storchaka
messages: + msg182119
2012-05-04 15:46:54pitrousetnosy: + pitrou
messages: + msg159941
2012-05-04 12:49:17pitrousetnosy: + ghaering

versions: + Python 2.7, Python 3.3
2012-05-04 06:31:01frankmillmancreate