Code

Opened 17 months ago

Last modified 13 months ago

#19312 new Bug

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: no 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 (1)

timestamp.diff (529 bytes) - added by mwaterfall 17 months ago.
Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP

Download all attachments as: .zip

Change History (4)

Changed 17 months ago by mwaterfall

Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP

comment:1 Changed 17 months 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 17 months 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 17 months ago by ramiro (previous) (diff)

comment:3 Changed 13 months 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.