classification
Title: sqlite3 timestamp adapter chokes on timezones
Type: enhancement Stage: resolved
Components: Library (Lib) Versions: Python 3.4
process
Status: closed Resolution: rejected
Dependencies: Superseder:
Assigned To: ghaering Nosy List: acdha, belopolsky, berker.peksag, ghaering, jacques-piguet, lemburg, pitrou, r.david.murray, vajrasky
Priority: normal Keywords: easy, patch

Created on 2013-09-22 00:03 by acdha, last changed 2016-03-27 17:00 by berker.peksag. This issue is now closed.

Files
File name Uploaded Description Edit
add_timezone_support_for_sqlite3_datetime_adapter.patch vajrasky, 2013-10-20 14:12 Initial patch by Chris Adams (acdha). Added support for negative timezone, doc, unit test and coding nitpick. review
Messages (10)
msg198234 - (view) Author: Chris Adams (acdha) Date: 2013-09-22 00:03
If you use detect_types=sqlite3.PARSE_DECLTYPES with sqlite3 and insert a timezone-aware datetime instance, you will get a ValueError if you attempt to read it back out: 

 File "/usr/local/Cellar/python3/3.3.2/Frameworks/Python.framework/Versions/3.3/lib/python3.3/sqlite3/dbapi2.py", line 68, in convert_timestamp
    hours, minutes, seconds = map(int, timepart_full[0].split(b":"))
ValueError: invalid literal for int() with base 10: '03+00'

Although this immediately gets into the thorny stdlib timezone support situation, it's extremely annoying to have the out-of-the-box module break round-tripping data and it looks like support for simple UTC offsets isn't too horrible – something like https://gist.github.com/acdha/6655391 works in very limited testing:

def tz_aware_timestamp_adapter(val):
    datepart, timepart = val.split(b" ")
    year, month, day = map(int, datepart.split(b"-"))

    if b"+" in timepart:
        timepart, tz_offset = timepart.rsplit(b"+", 1)
        if tz_offset == b'00:00':
            tzinfo = datetime.timezone.utc
        else:
            hours, minutes = map(int, tz_offset.split(b':', 1))
            tzinfo = datetime.timezone(datetime.timedelta(hours=hours, minutes=minutes))
    else:
        tzinfo = None

    timepart_full = timepart.split(b".")
    hours, minutes, seconds = map(int, timepart_full[0].split(b":"))

    if len(timepart_full) == 2:
        microseconds = int('{:0<6.6}'.format(timepart_full[1].decode()))
    else:
        microseconds = 0

    val = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds, tzinfo)

    return val

sqlite3.register_converter('timestamp', tz_aware_timestamp_adapter)
msg198269 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2013-09-22 12:02
This would be an useful improvement. Do you want to post a patch? See guidelines at http://docs.python.org/devguide/
msg198288 - (view) Author: Alexander Belopolsky (belopolsky) * (Python committer) Date: 2013-09-22 16:04
Sounds like a reasonable request, but the proposed code does not seem to work for the Eastern hemisphere (negative tz offsets.)

I am not very familiar with sqlite module.  What timestamp format does it use?  Isn't it some varian of ISO 3339?  See issue 15873.
msg200590 - (view) Author: Vajrasky Kok (vajrasky) * Date: 2013-10-20 14:12
Added patch to add timezone support for sqlite3 datetime adapter.
msg245836 - (view) Author: Jacques-D. Piguet (jacques-piguet) Date: 2015-06-26 05:29
I just hit the problem in Python 2.7.
The patch given here is not applicable due to missing class "datetime.timezone" in Python before 3.2.

Question 1: when will the patch be applied in Python 3?
Question 2: any chance to get a solution in Python 2?
msg248826 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-08-19 09:53
I'm -1 on adding timezone to the adapters.
msg248834 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2015-08-19 13:03
Can you expand on why you are -1, Gerhard?
msg249009 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-08-23 16:10
I'm -1 because I believe that ultimately, adapters and converters were a mistake to add to pysqlite. That's why I deprecated them in pysqlite 2.8.0.

Do you know what would be the correct step to propose a deprecation in the sqlite3 module of Python proper? Is a PEP necessary?
msg254314 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2015-11-07 23:38
Just open an issue to propose the deprecation, if they aren't part of the DBAPI.  We wouldn't actually remove them, though, until after 2.7 goes out of maintenance (if then...we still haven't quite decided what we're going to do about removals post 2.7).
msg262526 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2016-03-27 17:00
> Do you know what would be the correct step to propose a deprecation in the sqlite3 module of Python proper?

I've opened issue 26651 (patch included) to deprecate them.
History
Date User Action Args
2016-03-27 17:00:12berker.peksagsetstatus: open -> closed

type: crash -> enhancement

nosy: + berker.peksag
messages: + msg262526
resolution: rejected
stage: needs patch -> resolved
2015-11-07 23:38:57r.david.murraysetmessages: + msg254314
2015-08-23 16:10:51ghaeringsetmessages: + msg249009
2015-08-19 13:03:46r.david.murraysetmessages: + msg248834
2015-08-19 09:53:37ghaeringsetassignee: ghaering
messages: + msg248826
2015-06-26 05:29:52jacques-piguetsettype: enhancement -> crash

messages: + msg245836
nosy: + jacques-piguet
2013-10-20 14:12:56vajraskysetfiles: + add_timezone_support_for_sqlite3_datetime_adapter.patch

nosy: + vajrasky
messages: + msg200590

keywords: + patch
2013-09-22 16:04:22belopolskysetkeywords: + easy

messages: + msg198288
2013-09-22 12:02:02pitrousetversions: + Python 3.4, - Python 3.3
nosy: + belopolsky, pitrou, lemburg, ghaering

messages: + msg198269

type: enhancement
stage: needs patch
2013-09-22 00:32:04r.david.murraysetnosy: + r.david.murray
2013-09-22 00:03:21acdhacreate