Why does the Django time zone setting effect epoch time?
Question: I have a small Django project that imports data dumps from MongoDB into MySQL.
Inside these Mongo dumps are dates stored in epoch time. I would expect epoch
time to be the same regardless of time zone but what I am seeing is that the
Django
[TIME_ZONE](https://docs.djangoproject.com/en/dev/ref/settings/#std%3asetting-
TIME_ZONE) setting has an effect on the data created in MySQL.

I have been testing my database output with the MySQL
[UNIX_TIMESTAMP](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-
functions.html#function_unix-timestamp) function. If I insert a date with the
epoch of `1371131402880` (this includes milliseconds) I have my timezone set
to `'America/New_York'`, UNIX_TIMESTAMP gives me `1371131402`, which is the
same epoch time excluding milliseconds. However if I set my timezone to
`'America/Chicago'` I get `1371127802`.

This is my code to convert the epoch times into Python `datetime` objects,

    
    
    from datetime import datetime
    from django.utils.timezone import utc
    
    secs = float(epochtime) / 1000.0
    dt = datetime.fromtimestamp(secs)
    

I tried to fix the issue by putting an explict timezone on the `datetime`
object,

    
    
    # epoch time is in UTC by default
    dt = dt.replace(tzinfo=utc)
    

[PythonFiddle for the code](http://pythonfiddle.com/tests-for-epoch-issue)

I've tested this Python code in isolation and it gives me the expected
results. However it does not give the correct results after inserting these
object into MySQL through a Django model
[DateTimeField](https://docs.djangoproject.com/en/dev/ref/models/fields/#datetimefield)
field.

Here is my MySQL query,

    
    
    SELECT id, `date`, UNIX_TIMESTAMP(`date`) FROM table
    

I test this by comparing the unix timestamp column in the result of this query
against the MongoDB JSON dumps to see if the epoch matches.

What exactly is going on here? Why should timezone have any effect on epoch
times?

Just for reference, I am using Django 1.5.1 and MySQL-python 1.2.4. I also
have the Django
[USE_TZ](https://docs.djangoproject.com/en/dev/topics/i18n/timezones/) flag
set to `true`.


Answer: I am no python or Django guru, so perhaps someone can answer better than me.
But I will take a guess at it anyway.

You said that you were storing it in a Django `DateTimeField`, which according
to [the documents you
referenced](https://docs.djangoproject.com/en/dev/ref/models/fields/#datetimefield),
stores it as a Python `datetime`.

Looking at [the docs for
`datetime`](http://docs.python.org/3.3/library/datetime.html), I think the key
is understanding the difference between "naive" and "aware" values.

And then researching further, I came across [this excellent
reference](https://docs.djangoproject.com/en/dev/topics/i18n/timezones/). Be
sure the read the second section, "Naive and aware datetime objects". That
gives a bit of context to how much of this is being controlled by Django.
Basically, by setting `USE_TZ = true`, you are asking Django to use _aware_
datetimes instead of _naive_ ones.

So then I looked back at you question. You said you were doing the following:

    
    
    dt = datetime.fromtimestamp(secs)
    dt = dt.replace(tzinfo=utc)
    

Looking at the
[fromtimestamp](http://docs.python.org/3.3/library/datetime.html#datetime.datetime.fromtimestamp)
function documentation, I found this bit of text:

> If optional argument `tz` is `None` or not specified, the `timestamp` is
> converted to the platform’s local date and time, and the returned `datetime`
> object is naive.

So I think you could do this:

    
    
    dt = datetime.fromtimestamp(secs, tz=utc)
    

Then again, right below that function, the docs show `utcfromtimestamp`
function, so maybe it should be:

    
    
    dt = datetime.utcfromtimestamp(secs)
    

I don't know enough about python to know if these are equivalent or not, but
you could try and see if either makes a difference.

Hopefully one of these will make a difference. If not, please let me know. I'm
intimately familiar with date/time in JavaScript and in .Net, but I'm always
interested in how these nuances play out differently in other platforms, such
as Python.

## Update

Regarding the MySQL portion of the question, take a look at [this
fiddle](http://sqlfiddle.com/#!2/bbcfa/1).

    
    
    CREATE TABLE foo (`date` DATETIME);
    INSERT INTO foo (`date`) VALUES (FROM_UNIXTIME(1371131402));
    
    SET TIME_ZONE="+00:00";
    select `date`, UNIX_TIMESTAMP(`date`) from foo;
    
    SET TIME_ZONE="+01:00";
    select `date`, UNIX_TIMESTAMP(`date`) from foo;
    

Results:

    
    
    DATE                           UNIX_TIMESTAMP(`DATE`)
    June, 13 2013 13:50:02+0000    1371131402
    June, 13 2013 13:50:02+0000    1371127802
    

It would seem that the behavior of `UNIX_TIMESTAMP` function is indeed
affected by the MySQL `TIME_ZONE` setting. That's not so surprising, since
it's in the documentation. What's surprising is that the string output of the
`datetime` has the same UTC value regardless of the setting.

Here's what I think is happening. In the docs for the `UNIX_TIMESTAMP`
function, it says:

> `date` may be a `DATE` string, a `DATETIME` string, a `TIMESTAMP`, or a
> number in the format `YYMMDD` or `YYYYMMDD`.

Note that it doesn't say that it can be a `DATETIME` \- it says it can be a
`DATETIME` **_string_**. So I think the actual value being implicitly
converted to a string before being passed into the function.

So now look at [this updated fiddle](http://sqlfiddle.com/#!2/bbcfa/2) that
converts explicitly.

    
    
    SET TIME_ZONE="+00:00";
    select `date`, convert(`date`, char), UNIX_TIMESTAMP(convert(`date`, char)) from foo;
    
    SET TIME_ZONE="+01:00";
    select `date`, convert(`date`, char), UNIX_TIMESTAMP(convert(`date`, char)) from foo;
    

Results:

    
    
    DATE                           CONVERT(`DATE`, CHAR)  UNIX_TIMESTAMP(CONVERT(`DATE`, CHAR))
    June, 13 2013 13:50:02+0000    2013-06-13 13:50:02    1371131402
    June, 13 2013 13:50:02+0000    2013-06-13 13:50:02    1371127802
    

You can see that when it converts to character data, it strips away the
offset. So of course, it makes sense now that when `UNIX_TIMESTAMP` takes this
value as input, it is assuming the local time zone setting and thus getting a
different UTC timestamp.

Not sure if this will help you or not. You need to dig more into exactly how
Django is calling MySQL for both the read and the write. Does it actually use
the `UNIX_TIMESTAMP` function? Or was that just what you did in testing?

