CODEWITHSUNDEEP
mysqldatetimeepoch
Don Rhummy
Don Rhummy

Reputation: 25830

Why does the timezone of the server affect MySQL reading of FROM_UNIXTIME epochs?

I thought that if you passed MySQL an Epoch timestamp (as a long) and used FROM_UNIXTIME() to get it as a comparable date, that the timezone of the server wouldn't matter. But it does (at least in our setup - do we need a specific configuration?).

Using MySQL Version: mysql Ver 14.14 Distrib 5.5.48-37.8, for Linux (x86_64) using readline 5.1

I start with my server set to UTC.

When I run a query such as:

SELECT COUNT(*) FROM Orders
WHERE startTime BETWEEN FROM_UNIXTIME(1486509895) AND FROM_UNIXTIME(1486596296);

I get 127 orders.

I then switch my timezone to be EST:

sudo mv /etc/localtime /etc/localtime.bak
sudo ln -s /usr/share/zoneinfo/America/New_York /etc/localtime
sudo service mysql3306 restart

I then run the query again, and this time I get 196 orders!

Why does my server's timezone affect it? Epochs should not be affected by that.

Upvotes: 0

Views: 191

Answers (2)

shmosel
shmosel

Reputation: 50726

Of course the time zone matters. FROM_UNIXTIME() converts an instant in time to a date/time representation. But a given instant can represent a different date or time depending on the time zone. As the documentation explicitly states:

The value is expressed in the current time zone.

Upvotes: 1

tadman
tadman

Reputation: 211610

The documentation for FROM_UNIXTIMESTAMP() clearly states that:

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

This is why I strongly recommend you run your server in UTC and do any conversions in your application or by specifying an offset for your queries.

Epoch times (e.g. UNIX time_t) are expressed in UTC but are always rendered in local time if you have a time-zone specified in your environment, be it MySQL or otherwise. The raw numerical value doesn't change depending on your time-zone, in fact it has no concept of a time-zone at all, but converting that to a string in YYYY-MM-DD format absolutely requires a time-zone to render anything meaningful.

Upvotes: 2

Related Questions