Repository navigation
Default TIMESTAMP converter in sqlite3 ignores UTC offset #89498
Description
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']
Activity
iafisher commentedon Sep 30, 2021
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 commentedon Oct 2, 2021
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 commentedon Oct 4, 2021
Replacing the timestamp converter with datetime.datetime.fromisoformat sounds good to me.
iafisher commentedon Oct 5, 2021
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.
[-]Default TIMESTAMP converter in sqlite3 ignores time zone[/-][+]Default TIMESTAMP converter in sqlite3 ignores UTC offset[/+][-]Default TIMESTAMP converter in sqlite3 ignores time zone[/-][+]Default TIMESTAMP converter in sqlite3 ignores UTC offset[/+]erlend-aasland commentedon Oct 5, 2021
True.
Another option could be to deprecate the current behaviour and then change it to being timezone aware in Python 3.13.
iafisher commentedon Oct 5, 2021
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 commentedon Oct 5, 2021
Please, go ahead :)
However, I think this should be discussed on Discourse first (open a topic in the Core Development category).
iafisher commentedon Oct 6, 2021
Okay, I started a discussion here: https://discuss.python.org/t/fixing-sqlite-timestamp-converter-to-handle-utc-offsets/10985
erlend-aasland commentedon Oct 7, 2021
See also:
(Adding Berker to nosy list.)
ambv commentedon Oct 29, 2021
New changeset 3877fc0 by Ian Fisher in branch 'main':
bpo-45335: Add note to
sqlite3docs about "timestamp" converter (GH-29200)3877fc0
ambv commentedon Oct 29, 2021
New changeset 8ea665c by Miss Islington (bot) in branch '3.10':
bpo-45335: Add note to
sqlite3docs about "timestamp" converter (GH-29200) (GH-29319)8ea665c
ambv commentedon Oct 29, 2021
New changeset e156031 by Miss Islington (bot) in branch '3.9':
bpo-45335: Add note to
sqlite3docs about "timestamp" converter (GH-29200) (GH-29320)e156031
ambv commentedon Oct 29, 2021
Based on the decision from Discourse, the current behavior has only been documented and the actual issue is "wontfix".
Thanks for pursuing this, Ian! ✨ 🍰 ✨