Search code examples
mysqlconvert-tz

Converting a UTC time string to Unix time

In my database, I'm storing time as a BIGINT UTC value.

In my test data SQL dump I'd like to see the time in UTC for readability. However, the MySQL unix_timestamp function expects time in the local time zone, which I'm trying to avoid.

I've looked around and the only way I've found of converting the UTC time string to a unix time stamp is using this expression:

SELECT unix_timestamp(CONVERT_TZ('2011-08-08 06:00:00','UTC',@@global.time_zone))

Is there a better way to do this?

Solution

  • If you stored it as a timestamp instead of a bigint it would happen automatically just by setting the timezone.

    Also, I don't really understand what you are doing. You are storing unixtime as your bigint column correct? So I don't get why you are using the unix_timestamp() function - that converts TO a unixtime, not from it.

    I guess this is what you need:

    SET time_zone = '+0:00';
SELECT FROM_UNIXTIME(col) FROM table;