Is Django corrupting timezone-aware DateTimeField when saving it to the Database?

Question

I have a Django model as described here

I create and save an instance of this model:

>>> from django.db.models import Max, F, Func
>>> from django.conf import settings
>>> import pytz, datetime
>>> from myapp.models import myModel

>>> myModel.objects.all().delete()

>>> myModel.objects.create(
    my_date=datetime.datetime(2037,4,2,15,18,17,tzinfo=pytz.UTC), 
    my_string="asda"
)
<myModel: myModel object>

Then I try to retrieve the instance I just inserted, but I get the DateTimeField in native format and after applying UNIX_TIMESTAMP:

>>> x = myModel.objects.values('my_string').aggregate(
    max1=Max('my_date'), 
    max2=Max(Func(F('my_date'), function='UNIX_TIMESTAMP'))
)

>>> x
{
    'max2': Decimal('2122848857.000000'), 
    'max1': datetime.datetime(2037, 4, 8, 20, 14, 17, tzinfo=<UTC>)
}

>>> datetime.datetime.utcfromtimestamp(x["max2"])
datetime.datetime(2037, 4, 9, 0, 14, 17)


>>> pytz.timezone(settings.TIME_ZONE)
<DstTzInfo 'America/New_York' LMT-1 day, 19:04:00 STD>
>>>

If you convert 2122848857 back to a DateTime, you get 2037-04-09T00:14:17+00:00. This is 4 hours greater than the time I actually inserted. Why? How to correct this seeming corruption? My machine's timezone is EDT which is 4 hours behind UTC. But that still doesn't explain why Django is saving this UTC time as if it was in my local timezone.

The `time_zone` setting of the MySQL session will impact values stored and retrieved from `timestamp` and `datetime` columns. To see the current setting of the session: **`SHOW VARIABLES LIKE 'time_zone'`** To change the setting (for the current connection only) **`SET time_zone = '+00:00'`**. http://stackoverflow.com/questions/4805778/django-time-zone-issue — spencer7593, Jul 08 '15 at 21:58

madzohan · Accepted Answer · 2015-07-14T08:14:54.597

1

import pytz, datetime
from django.db.models import Max, F, Func
from django.conf import settings
from myapp.models import myModel

local_tz = pytz.timezone(settings.TIME_ZONE)

local_datetime = local_tz.localize(datetime.datetime(2037, 4, 8, 20, 14, 17), is_dst=None)
utc_datetime = local_datetime.astimezone(pytz.UTC)
# datetime.datetime(2037, 4, 9, 0, 14, 17, tzinfo=<UTC>)

MyModel.objects.create(my_date=utc_datetime)

x = MyModel.objects.aggregate(max1=Max('my_date'),max2=Max(Func(F('my_date'), function='UNIX_TIMESTAMP')))

pytz.UTC.localize(datetime.datetime.fromtimestamp(x['max2'])).astimezone(local_tz) == x['max1'].astimezone(local_tz)

edited Jul 14 '15 at 08:14

answered Jul 08 '15 at 21:32

madzohan

11,488
9
40
67

But the datetime you entered (2013-12-11-15:18:17) is timezone naive. I need to put in a date that is explicitly UTC. Adding the tzinfo argument to this datetime causes it to fail. – Saqib Ali Jul 08 '15 at 22:06
Still not working unfortunately @madzohan: https://gist.github.com/saqib-zmi/7c1c63a4971223c523e4 – Saqib Ali Jul 09 '15 at 03:06
sorry, now it should work (at last I've tested this :D ) – madzohan Jul 14 '15 at 08:10

score 1 · Answer 2 · answered Jul 12 '15 at 04:51

The primary difference between datetime and timestamp is that timestamp will automatically store a value as UTC, using the current mysql time_zone setting, and datetime will ignore the time_zone setting when inserting and retrieving records.

You're using a datetime field, however you're also using the mysql UNIX_TIMESTAMP function against that field. When you do that, the docs explain that the server interprets the value as a local time value, based on the time_zone setting. This is the source of the conversion discrepancy.

You have two choices.

Ensure the time_zone session variable is set to UTC before running your query.
Store the value into a timestamp field instead of a datetime field.

Sorry, I don't know much about Django to answer with the exact code. This is from a mysql perspective. According to [this answer](http://stackoverflow.com/a/17442224/634824) you may be able to take advantage of a `USE_TZ` setting. — Matt Johnson-Pint, Jul 12 '15 at 04:53

score 0 · Answer 3 · answered May 05 '19 at 22:07

Yes. Some of the data is discarded.

Django DateTime field stored data in a database DateTime storage that lacks timezone information thus the information is removed before storage.

This is whet the manual says:

Note that if you set this to point to a DateTimeField, only the date portion of the > field will be considered. Besides, when USE_TZ is True, the check will be performed > in the current time zone at the time the object gets saved.

The proper way to store full datetime date would be to use a DateTimeOffset field - that can be found in MS-Sql and others. But this is not supported (yet?)

Is Django corrupting timezone-aware DateTimeField when saving it to the Database?

3 Answers3

Linked