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

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 2

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

Answer 3

If you cannot control the timezone of your session^*, from_unixtime and some other date/time functions are fundamentally broken and should be avoided. Fortunately, date arithmetic completely ignores session timezone, so you can simply do:

select '1970-01-01' + interval 1277942400 second

or the equivalent

select date_add('1970-01-01', interval 1277942400 second)

^{* this can be hard; some clients "helpfully" will set a session timezone for you based on environment; other clients will automatically reestablish a dropped connection so you can't rely on setting timezone just when you connect and must set it with every statement}

Answer 4

if you are lazy like me and don't want to change global session timezone variables (moved to another server, forgot to change that there and hop, you are in troubles), session variables (used some module autoreconnecting to db and hop, your variable issued at connection is gone) and don't wish to load time zone stuff then i suppose this might help :).

select 
  now() as timezoned,
  date_add(
    FROM_UNIXTIME(0), interval 
      unix_timestamp()+TIMESTAMPDIFF(SECOND,NOW(),UTC_TIMESTAMP()) SECOND
  ) as utc

and it's going to work even with negative timestamps. and if you need to convert a specific column, just replace unix_timestamp() with your column containing the timestamp.

as a bonus, if you need to find a difference between datetime and now, while the column is in unix_timestamp (say, "utc"), just wrap it with TIMESTAMPdiff(SECOND, .., NOW()) and the day is saved :).

update: if you live in a region with a time savings and your time jumps, here is a thing i came up with, which is a much more simple one.

select from_unixtime(1277942400) as timezoned, date_add( '1970-01-01 00:00:00', interval 1277942400 SECOND ) as utc

much more simple :).

an alternative that is not so good:

select from_unixtime(1277942400) as timezoned, date_add( FROM_UNIXTIME(0), interval 1277942400+ (UNIX_TIMESTAMP('1970-1-2')-UNIX_TIMESTAMP(0)-24*3600) SECOND ) as utc