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