0

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.

Drahcir
  • 11,772
  • 24
  • 86
  • 128

1 Answers1

1

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.

Community
  • 1
  • 1
n34_panda
  • 2,577
  • 5
  • 24
  • 40
  • Thanks - `CONVERT_TZ(from_unixtime(1395928430), @@session.time_zone, '+00:00')` – Drahcir Mar 27 '14 at 13:58
  • Yeah that's actually right, SYSTEM is a valid timezone and can be converted using now() etc. I should have highlighted that, glad it's working. – n34_panda Mar 27 '14 at 14:01
  • Also I found that I can do `SET @@session.time_zone := '+00:00'; SELECT FROM_UNIXTIME(1395928430)` which would be handy if inserting multiple columns. – Drahcir Mar 27 '14 at 14:01