| 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) *  |
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) * |
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) * |
Date: 2013-02-23 18:13 |
| Applied, thanks! |
|
|
| msg182856 - (view) |
Author: Serhiy Storchaka (serhiy.storchaka) * |
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) * |
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) * |
Date: 2013-02-26 19:48 |
| Fixed. |
|
|