Mysql FROM_UNIXTIME as UTC

Question

How can i get

FROM_UNIXTIME

as UTC/GMT in mysql? The date is returned in the timezone of the connection.

I don't want to change the timezone of the connection.

Answer 1

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:")

Answer 2

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 

Answer 3

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