Mysql FROM_UNIXTIME as UTC

I know this has been answered but still I'd like to contribute:

If you wan't your server to produce UTC timezone time with functions like NOW() and FROM_UNIXTIME() etc., you have to set the time_zone like @Matt Johnson said.

There's a thing he didn't mention: SET time_zone = timezone; sets the timezone for the current connection only. Use SET GLOBAL time_zone = '+00:00' if you want these functions to return/convert your dates in UTC by default so you didn't have to set the timezone every time you want to save some UTC based dates.

Check your current time_zone settings: SELECT @@global.time_zone, @@session.time_zone;

MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support


my solution was

SELECT CONVERT_TZ(FROM_UNIXTIME(1277942400), @@session.time_zone,'UTC')

if CONVERT_TZ returns null, make sure the timezone table of mysql is filled:

zypper install mysql-community-server-tools
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

You would be better off setting the time zone ahead of time:

SET time_zone='UTC';
select FROM_UNIXTIME(1277942400);

The reason is that conversions involving a local time zone can be lossy. There is an example of this in the docs here (see the 4th paragraph under the UNIX_TIMESTAMP() section starting with "Note:")

Tags:

Mysql

Timezone