#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)
Change History (8)
by , 12 years ago
Attachment: | timestamp.diff added |
---|
comment:1 by , 12 years ago
Easy pickings: | unset |
---|---|
Needs tests: | set |
Patch needs improvement: | set |
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → 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 by , 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.
comment:3 by , 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 , 10 years ago
Attachment: | 19312.diff added |
---|
comment:4 by , 10 years ago
Has patch: | set |
---|---|
Type: | Bug → Cleanup/optimization |
comment:5 by , 10 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Adds datetime+timezone conversion function for FIELD_TYPE.TIMESTAMP