Is Django corrupting timezone-aware DateTimeField

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.

Answer 1

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.

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

Answer 2

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)