Author acdha
Recipients acdha
Date 2013-09-22.00:03:20
SpamBayes Score -1.0
Marked as misclassified Yes
Message-id <>
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/", 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 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
            hours, minutes = map(int, tz_offset.split(b':', 1))
            tzinfo = datetime.timezone(datetime.timedelta(hours=hours, minutes=minutes))
        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()))
        microseconds = 0

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

    return val

sqlite3.register_converter('timestamp', tz_aware_timestamp_adapter)
Date User Action Args
2013-09-22 00:03:21acdhasetrecipients: + acdha
2013-09-22 00:03:21acdhasetmessageid: <>
2013-09-22 00:03:21acdhalinkissue19065 messages
2013-09-22 00:03:20acdhacreate