Default TIMESTAMP converter in sqlite3 ignores UTC offset

[iafisher]

BPO	45335
Nosy	@ambv, @berkerpeksag, @corona10, @miss-islington, @erlend-aasland, @iafisher, @MrFuppes
PRs	bpo-45335: Add note to sqlite3 docs about "timestamp" converter #29200 [3.10] bpo-45335: Add note to sqlite3 docs about "timestamp" converter (GH-29200) #29319 [3.9] bpo-45335: Add note to sqlite3 docs about "timestamp" converter (GH-29200) #29320
Files	timestamp.py

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2021-10-29.20:45:13.354>
created_at = <Date 2021-09-30.19:37:28.136>
labels = ['type-feature', 'library', '3.9', '3.10', '3.11']
title = 'Default TIMESTAMP converter in sqlite3 ignores UTC offset'
updated_at = <Date 2021-10-29.20:45:13.352>
user = 'https://github.com/iafisher'

bugs.python.org fields:

activity = <Date 2021-10-29.20:45:13.352>
actor = 'lukasz.langa'
assignee = 'none'
closed = True
closed_date = <Date 2021-10-29.20:45:13.354>
closer = 'lukasz.langa'
components = ['Library (Lib)']
creation = <Date 2021-09-30.19:37:28.136>
creator = 'iafisher'
dependencies = []
files = ['50324']
hgrepos = []
issue_num = 45335
keywords = ['patch']
message_count = 13.0
messages = ['402979', '403064', '403185', '403188', '403269', '403278', '403279', '403281', '403354', '405334', '405338', '405339', '405340']
nosy_count = 7.0
nosy_names = ['lukasz.langa', 'berker.peksag', 'corona10', 'miss-islington', 'erlendaasland', 'iafisher', 'MrFuppes']
pr_nums = ['29200', '29319', '29320']
priority = 'normal'
resolution = 'wont fix'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue45335'
versions = ['Python 3.9', 'Python 3.10', 'Python 3.11']

[iafisher]

The SQLite converter that the sqlite3 library automatically registers for TIMESTAMP columns (https://github.com/python/cpython/blob/main/Lib/sqlite3/dbapi2.py#L66) ignores the time zone even if it is present and always returns a naive datetime object.

I think that the converter should return an aware object if the time zone is present in the database. As it is, round trips of TIMESTAMP values from the database to Python and back might erase the original time zone info.

Now that datetime.datetime.fromisoformat is in Python 3.7, this should be easy to implement.

[iafisher]

Substitute "UTC offset" for "time zone" in my comment above.

I have attached a minimal Python program demonstrating data loss from this bug.

[erlend-aasland]

Replacing the timestamp converter with datetime.datetime.fromisoformat sounds good to me.

[iafisher]

Unfortunately fixing this will have to be considered a backwards-incompatible change, since Python doesn't allow naive and aware datetime objects to be compared, so if sqlite3 starts returning aware datetimes, existing code might break.

Alternatively, perhaps this could be fixed in conjunction with changing sqlite3's API to allow per-database converters and adapters. Then, the old global TIMESTAMP converter could be retained for compatibility with existing code, and new code could opt-in to a per-database TIMESTAMP converter with the correct behavior.

[erlend-aasland]

[...] if sqlite3 starts returning aware datetimes, existing code might break.

True.

[...] perhaps this could be fixed in conjunction with changing sqlite3's API to allow per-database converters and adapters

Another option could be to deprecate the current behaviour and then change it to being timezone aware in Python 3.13.

[iafisher]

Another option could be to deprecate the current behaviour and then change it to being timezone aware in Python 3.13.

This sounds like the simplest option.

I'd be interested in working on this myself, if you think it's something that a new CPython contributor could handle.

[erlend-aasland]

I'd be interested in working on this myself, if you think it's something that a new CPython contributor could handle.

Please, go ahead :)

However, I think this should be discussed on Discourse first (open a topic in the Core Development category).

[iafisher]

Okay, I started a discussion here: https://discuss.python.org/t/fixing-sqlite-timestamp-converter-to-handle-utc-offsets/10985

[erlend-aasland]

See also:

• bpo-19065: sqlite3 timestamp adapter chokes on timezones
• bpo-26651 Deprecate register_adapter() and register_converter() in sqlite3

(Adding Berker to nosy list.)