Skip to content

Default TIMESTAMP converter in sqlite3 ignores UTC offset #89498

Closed
Closed
Default TIMESTAMP converter in sqlite3 ignores UTC offset#89498
Labels
3.10 (EOL)end of life3.11only security fixes3.9 (EOL)end of lifestdlibStandard Library Python modules in the Lib/ directorytopic-sqlite3type-featureA feature request or enhancement

Description

@iafisher
iafisher
mannequin
opened on Sep 30, 2021
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']

    Activity

    1. iafisher commented on Sep 30, 2021

      @iafisher
      iafishermannequin
      on Sep 30, 2021
      MannequinAuthor

      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.

    2. iafisher
      added
      stdlibStandard Library Python modules in the Lib/ directory
      type-featureA feature request or enhancement
      on Sep 30, 2021

    3. iafisher commented on Oct 2, 2021

      @iafisher
      iafishermannequin
      on Oct 2, 2021
      MannequinAuthor

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

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

    4. erlend-aasland commented on Oct 4, 2021

      @erlend-aasland
      erlend-aasland
      on Oct 4, 2021
      Contributor

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

    5. iafisher commented on Oct 5, 2021

      @iafisher
      iafishermannequin
      on Oct 5, 2021
      MannequinAuthor

      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.

    6. iafisher
      changed the title [-]Default TIMESTAMP converter in sqlite3 ignores time zone[/-] [+]Default TIMESTAMP converter in sqlite3 ignores UTC offset[/+] on Oct 5, 2021
    7. iafisher
      changed the title [-]Default TIMESTAMP converter in sqlite3 ignores time zone[/-] [+]Default TIMESTAMP converter in sqlite3 ignores UTC offset[/+] on Oct 5, 2021

    8. erlend-aasland commented on Oct 5, 2021

      @erlend-aasland
      erlend-aasland
      on Oct 5, 2021
      Contributor

      [...] 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.

    9. iafisher commented on Oct 5, 2021

      @iafisher
      iafishermannequin
      on Oct 5, 2021
      MannequinAuthor

      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.

    10. erlend-aasland commented on Oct 5, 2021

      @erlend-aasland
      erlend-aasland
      on Oct 5, 2021
      Contributor

      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).

    11. iafisher commented on Oct 6, 2021

      @iafisher
      iafishermannequin
      on Oct 6, 2021
      MannequinAuthor

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

    12. erlend-aasland commented on Oct 7, 2021

      @erlend-aasland
      erlend-aasland
      on Oct 7, 2021
      Contributor

      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.)

    13. ambv commented on Oct 29, 2021

      @ambv
      ambv
      on Oct 29, 2021
      Contributor

      New changeset 3877fc0 by Ian Fisher in branch 'main':
      bpo-45335: Add note to sqlite3 docs about "timestamp" converter (GH-29200)
      3877fc0

    14. ambv commented on Oct 29, 2021

      @ambv
      ambv
      on Oct 29, 2021
      Contributor

      New changeset 8ea665c by Miss Islington (bot) in branch '3.10':
      bpo-45335: Add note to sqlite3 docs about "timestamp" converter (GH-29200) (GH-29319)
      8ea665c

    15. ambv commented on Oct 29, 2021

      @ambv
      ambv
      on Oct 29, 2021
      Contributor

      New changeset e156031 by Miss Islington (bot) in branch '3.9':
      bpo-45335: Add note to sqlite3 docs about "timestamp" converter (GH-29200) (GH-29320)
      e156031

    16. ambv commented on Oct 29, 2021

      @ambv
      ambv
      on Oct 29, 2021
      Contributor

      Based on the decision from Discourse, the current behavior has only been documented and the actual issue is "wontfix".

      Thanks for pursuing this, Ian! ✨ 🍰 ✨

    17. ambv
      added
      3.9 (EOL)end of life
      3.10 (EOL)end of life
      3.11only security fixes
      on Oct 29, 2021
    18. ambv
      closed this as completedon Oct 29, 2021
    19. ambv
      added
      3.9 (EOL)end of life
      3.10 (EOL)end of life
      3.11only security fixes
      on Oct 29, 2021
    20. ambv
      closed this as completedon Oct 29, 2021
    21. ezio-melotti
      transferred this issue fromon Apr 10, 2022
    22. erlend-aasland
      added
      topic-sqlite3
      on May 16, 2022
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

    Metadata

    Metadata

    Assignees

    No one assigned

      Labels

      3.10 (EOL)end of life3.11only security fixes3.9 (EOL)end of lifestdlibStandard Library Python modules in the Lib/ directorytopic-sqlite3type-featureA feature request or enhancement

      Projects

      No projects

        Milestone

        No milestone

        Relationships

        None yet

        Development

        No branches or pull requests

        Issue actions