[Django] #19312: Time zone issue with MySQL timestamp fields
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Keywords: mysql timezone
Severity: Normal | timestamp datetime datetimefield
Triage Stage: Unreviewed | Has patch: 1
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/19312>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Component: Database layer | Version: 1.4
Severity: Normal | Triage Stage: Accepted
Keywords: mysql timezone | Needs documentation: 0
timestamp datetime datetimefield | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by aaugustin):
* needs_better_patch: => 1
* needs_tests: => 1
* easy: 1 => 0
* needs_docs: => 0
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Component: Database layer | Version: 1.4
Severity: Normal | Triage Stage: Accepted
Keywords: mysql timezone | Needs documentation: 0
timestamp datetime datetimefield | Patch needs improvement: 1
Has patch: 1 | UI/UX: 0
Needs tests: 1 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (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 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Component: Documentation | Version: 1.4
Severity: Normal | Resolution:
Keywords: mysql timezone | Triage Stage: Accepted
timestamp datetime datetimefield | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by aaugustin):
* needs_better_patch: 1 => 0
* has_patch: 1 => 0
* component: Database layer (models, ORM) => Documentation
* needs_tests: 1 => 0
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Cleanup/optimization | Version: 1.4
Component: Documentation | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: mysql timezone | Needs documentation: 0
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by timgraham):
* has_patch: 0 => 1
* type: Bug => Cleanup/optimization
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Cleanup/optimization | Version: 1.4
Component: Documentation | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: mysql timezone | Needs documentation: 0
timestamp datetime datetimefield | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"348c89cbfbe3b9be6e759d8d5696d4fde1827d9b"]:
{{{
#!CommitTicketReference repository=""
revision="348c89cbfbe3b9be6e759d8d5696d4fde1827d9b"
Fixed #19312 -- Documented MySQL TIMESTAMP columns cannot be used with
USE_TZ=True.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: mwaterfall | Owner: nobody
Cleanup/optimization | Version: 1.4
Component: Documentation | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: mysql timezone | Needs documentation: 0
timestamp datetime datetimefield | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"e920c900ec5189115ed8648c21952444c7790b34"]:
{{{
#!CommitTicketReference repository=""
revision="e920c900ec5189115ed8648c21952444c7790b34"
[1.7.x] Fixed #19312 -- Documented MySQL TIMESTAMP columns cannot be used
with USE_TZ=True.
Backport of 348c89cbfb from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/19312#comment:6>