Search code examples
mysqltimeutcepoch

Convert unix time to UTC datetime

I'm trying to insert a timestamp into the database, I need the timestamp to be in UTC time, not the server's time zone.

INSERT INTO my_table SET time = FROM_UNIXTIME(:epochTime)

But the newly inserted datetime value is not in UTC time, how can I do this?

Perhaps I should also use CONVERT_TZ, but I don't know the timezone from which I convert.

Solution

  • Yup I would look at CONVERT_TZ:

    http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

    To grab the timezone from mysql:

    mysql> SELECT @@global.time_zone, @@session.time_zone;

    Edit: The query below returns the timezone of the current session.

    select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

    URL for some info: How do I get the current time zone of MySQL?

    If the server has a different timezone, tell us your Operating system or location.