classification
Title: Default TIMESTAMP converter in sqlite3 ignores UTC offset
Type: enhancement Stage: resolved
Components: Library (Lib) Versions: Python 3.11, Python 3.10, Python 3.9
process
Status: closed Resolution: wont fix
Dependencies: Superseder:
Assigned To: Nosy List: MrFuppes, berker.peksag, corona10, erlendaasland, iafisher, lukasz.langa, miss-islington
Priority: normal Keywords: patch

Created on 2021-09-30 19:37 by iafisher, last changed 2021-10-29 20:45 by lukasz.langa. This issue is now closed.

Files
File name Uploaded Description Edit
timestamp.py iafisher, 2021-10-02 21:36
Pull Requests
URL Status Linked Edit
PR 29200 merged iafisher, 2021-10-24 13:01
PR 29319 merged miss-islington, 2021-10-29 20:17
PR 29320 merged miss-islington, 2021-10-29 20:17
Messages (13)
msg402979 - (view) Author: Ian Fisher (iafisher) * Date: 2021-09-30 19:37
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.
msg403064 - (view) Author: Ian Fisher (iafisher) * Date: 2021-10-02 21:36
Substitute "UTC offset" for "time zone" in my comment above.

I have attached a minimal Python program demonstrating data loss from this bug.
msg403185 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-10-04 22:35
Replacing the timestamp converter with datetime.datetime.fromisoformat sounds good to me.
msg403188 - (view) Author: Ian Fisher (iafisher) * Date: 2021-10-05 00:00
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.
msg403269 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-10-05 21:17
> [...] 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.
msg403278 - (view) Author: Ian Fisher (iafisher) * Date: 2021-10-05 23:06
> 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.
msg403279 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-10-05 23:10
> 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).
msg403281 - (view) Author: Ian Fisher (iafisher) * Date: 2021-10-06 00:33
Okay, I started a discussion here: https://discuss.python.org/t/fixing-sqlite-timestamp-converter-to-handle-utc-offsets/10985
msg403354 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-10-07 07:20
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.)
msg405334 - (view) Author: Łukasz Langa (lukasz.langa) * (Python committer) Date: 2021-10-29 20:17
New changeset 3877fc02f7a8801ba5ce0e94b6075b3fdd9778d0 by Ian Fisher in branch 'main':
bpo-45335: Add note to `sqlite3` docs about "timestamp" converter (GH-29200)
https://github.com/python/cpython/commit/3877fc02f7a8801ba5ce0e94b6075b3fdd9778d0
msg405338 - (view) Author: Łukasz Langa (lukasz.langa) * (Python committer) Date: 2021-10-29 20:41
New changeset 8ea665c730cd86a321c558c012bef84f454efa4f by Miss Islington (bot) in branch '3.10':
bpo-45335: Add note to `sqlite3` docs about "timestamp" converter (GH-29200) (GH-29319)
https://github.com/python/cpython/commit/8ea665c730cd86a321c558c012bef84f454efa4f
msg405339 - (view) Author: Łukasz Langa (lukasz.langa) * (Python committer) Date: 2021-10-29 20:42
New changeset e1560313d4d9bff8eba0f851ef325f7ee19f7ba9 by Miss Islington (bot) in branch '3.9':
bpo-45335: Add note to `sqlite3` docs about "timestamp" converter (GH-29200) (GH-29320)
https://github.com/python/cpython/commit/e1560313d4d9bff8eba0f851ef325f7ee19f7ba9
msg405340 - (view) Author: Łukasz Langa (lukasz.langa) * (Python committer) Date: 2021-10-29 20:45
Based on the decision from Discourse, the current behavior has only been documented and the actual issue is "wontfix".

Thanks for pursuing this, Ian! ✨ 🍰 ✨
History
Date User Action Args
2021-10-29 20:45:13lukasz.langasetstatus: open -> closed
versions: + Python 3.9, Python 3.10, Python 3.11
messages: + msg405340

resolution: wont fix
stage: patch review -> resolved
2021-10-29 20:42:47lukasz.langasetmessages: + msg405339
2021-10-29 20:41:53lukasz.langasetmessages: + msg405338
2021-10-29 20:17:38miss-islingtonsetpull_requests: + pull_request27590
2021-10-29 20:17:34miss-islingtonsetnosy: + miss-islington
pull_requests: + pull_request27589
2021-10-29 20:17:30lukasz.langasetnosy: + lukasz.langa
messages: + msg405334
2021-10-24 13:01:37iafishersetkeywords: + patch
stage: patch review
pull_requests: + pull_request27469
2021-10-07 07:20:22erlendaaslandsetnosy: + berker.peksag
messages: + msg403354
2021-10-06 00:33:53iafishersetmessages: + msg403281
2021-10-05 23:10:09erlendaaslandsetmessages: + msg403279
2021-10-05 23:06:35iafishersetmessages: + msg403278
2021-10-05 21:17:02erlendaaslandsetmessages: + msg403269
2021-10-05 00:00:22iafishersetmessages: + msg403188
title: Default TIMESTAMP converter in sqlite3 ignores time zone -> Default TIMESTAMP converter in sqlite3 ignores UTC offset
2021-10-04 22:35:06erlendaaslandsetmessages: + msg403185
2021-10-04 09:52:14MrFuppessetnosy: + MrFuppes
2021-10-03 11:12:23corona10setnosy: + corona10, erlendaasland
2021-10-02 21:36:17iafishersetfiles: + timestamp.py

messages: + msg403064
2021-09-30 19:37:28iafishercreate