Opened 2 years ago

Closed 8 months ago

Last modified 8 months 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 2 years ago.
Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP
19312.diff (736 bytes) - added by timgraham 9 months ago.

Download all attachments as: .zip

Change History (8)

Changed 2 years ago by mwaterfall

Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP

comment:1 Changed 2 years ago by aaugustin

  • Easy pickings unset
  • Needs documentation unset
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to Bug

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 2 years ago by ramiro

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 2 years ago by ramiro (previous) (diff)

comment:3 Changed 2 years ago by aaugustin

  • Component changed from Database layer (models, ORM) to 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 9 months ago by timgraham

comment:4 Changed 9 months ago by timgraham

  • Has patch set
  • Type changed from Bug to Cleanup/optimization

comment:5 Changed 8 months ago by Tim Graham <timograham@…>

  • Resolution set to fixed
  • Status changed from new to closed

In 348c89cbfbe3b9be6e759d8d5696d4fde1827d9b:

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

comment:6 Changed 8 months 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