MySQL: Convert UNIX_TIMESTAMP to UTC DATETIME

We just learned that when you insert a UNIX_TIMESTAMP into MySQL that it will keep the UTC version of the timestamp, which is good.  However, when you use FROM_UNIXTIME() to convert your Unix timestamp into a datetime string, it will localize the datetime to mysql’s timezone.

So, a couple things here:

1. System time and MySQL time are not always the same
2. UTC and MySQL time are not the same
3. UNIX_TIMESTAMP() is not the same actual time as FROM_UNIXTIME(UNIX_TIMESTAMP())

So, there is actually a somewhat easy solution (once you know it) to fix this issue.

MySQL provides a CONVERT_TZ(datetime, from, to) function.

Our first solution was to just get the timezone of the server and drop that into the code:

This does not work if we do and install on a server not running on MST.  So, the better solution is:

The variable “@@global.time_zone” is a MySQL system variable which will return the timezone MySQL is running on.  With this, we have now just converted ALL of our datetimes to UTC time, and then we can adjust them to our users as we wish.

If you wanted to pass the user’s timezone into the function, you would just need to know the UTC offset and replace ‘+00:00′ with that offset.

I.E. MST would be “-07:00″