Opened 12 years ago

Closed 10 years ago

Last modified 10 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 12 years ago.
Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP
19312.diff (736 bytes ) - added by Tim Graham 10 years ago.

Download all attachments as: .zip

Change History (8)

by mwaterfall, 12 years ago

Attachment: timestamp.diff added

Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP

comment:1 by Aymeric Augustin, 12 years ago

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 by Ramiro Morales, 12 years ago

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

Last edited 12 years ago by Ramiro Morales (previous) (diff)

comment:3 by Aymeric Augustin, 12 years ago

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.

by Tim Graham, 10 years ago

Attachment: 19312.diff added

comment:4 by Tim Graham, 10 years ago

Has patch: set
Type: BugCleanup/optimization

comment:5 by Tim Graham <timograham@…>, 10 years ago

Resolution: fixed
Status: newclosed

In 348c89cbfbe3b9be6e759d8d5696d4fde1827d9b:

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

comment:6 by Tim Graham <timograham@…>, 10 years ago

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