Opened 4 years ago

Closed 2 years ago

Last modified 2 years ago

#19312 closed Cleanup/optimization (fixed)

Time zone issue with MySQL timestamp fields

Reported by: mwaterfall Owned by: nobody
Component: Documentation Version: 1.4
Severity: Normal Keywords: mysql timezone timestamp datetime datetimefield
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

MySQL timestamp fields are supported by Django ORM's DateTimeField, however if time zone support is enabled Django is not ensuring the Python datetime objects (from MySQLdb) have a default tzinfo applied to them (UTC) in the same way that it does when dates come from a datetime MySQL field.

Because of this, when saving the model after it has just been read from the database, Django throws a naive datetime / time zone warning from within the field's get_prep_value method:

RuntimeWarning: DateTimeField received a naive datetime (2012-11-17 16:08:44) while time zone support is active.

I have tracked the problem down to Django's MySQL cursor wrapper class, which applies alternative conversion functions for certain field types. The parse_datetime_with_timezone_support function ensures Python datetime objects always have a default tzinfo if time zone support is enabled. The FIELD_TYPE.DATETIME type gets handled by this function, however FIELD_TYPE.TIMESTAMP doesn't, and the naive datetime objects slip through and produce errors.

Attachments (2)

timestamp.diff (529 bytes) - added by mwaterfall 4 years ago.
Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP
19312.diff (736 bytes) - added by Tim Graham 2 years ago.

Download all attachments as: .zip

Change History (8)

Changed 4 years ago by mwaterfall

Attachment: timestamp.diff added

Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP

comment:1 Changed 4 years ago by Aymeric Augustin

Easy pickings: unset
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

I assume you're using Django with an existing MySQL database whose schema you don't control.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval (see http://dev.mysql.com/doc/refman/5.5/en/datetime.html).

I'm not sure how the MySQLdb driver deals with this, but most likely applying the UTC timezone is wrong, unless we also set the connection to UTC.

Of course the patch needs tests.

comment:2 Changed 4 years ago by Ramiro Morales

Exactly, the only explicit support Django currently has for TIMESTAMP is the fact that the initial models.py (that, if possible, one should use as a base for a production-ready one) created by inspectdb maps columns having of such data type to DateTimeField model field.

I'm not sure Django needs to to anything extra to even at the ORM the differences between DATETIME and TIMESTAMP DB colimnt types, it may be the case there is no way to model the latter when timezone-support is active or that doing so leads to data loss.

Version 0, edited 4 years ago by Ramiro Morales (next)

comment:3 Changed 4 years ago by Aymeric Augustin

Component: Database layer (models, ORM)Documentation
Has patch: unset
Needs tests: unset
Patch needs improvement: unset

Yes, I think the only reasonable option for dealing with TIMESTAMP fields is USE_TZ = False. Otherwise both MySQL and Django attempt to convert from UTC to local time.

Since Django doesn't create TIMESTAMP fields this is something we could document in the database-specific notes.

Changed 2 years ago by Tim Graham

Attachment: 19312.diff added

comment:4 Changed 2 years ago by Tim Graham

Has patch: set
Type: BugCleanup/optimization

comment:5 Changed 2 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In 348c89cbfbe3b9be6e759d8d5696d4fde1827d9b:

Fixed #19312 -- Documented MySQL TIMESTAMP columns cannot be used with USE_TZ=True.

comment:6 Changed 2 years ago by Tim Graham <timograham@…>

In e920c900ec5189115ed8648c21952444c7790b34:

[1.7.x] Fixed #19312 -- Documented MySQL TIMESTAMP columns cannot be used with USE_TZ=True.

Backport of 348c89cbfb from master

Note: See TracTickets for help on using tickets.
Back to Top