1

I create a table:

CREATE TABLE IF NOT EXISTS messages
  (id INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
   someText TEXT,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id))

And I set the timezone for the session:

SET TIME_ZONE = '-00:00'

The timezone is set successfully (checked with SELECT @@session.time_zone; ).

However when I insert a new row, the created and updated field have a local offset (e.g. it writes 2017-04-06 23:00:00 while the UTC time is actually 2017-04-06 21:00:00).

Any idea what could be wrong?

Jiam30
  • 163
  • 1
  • 7
  • What OS are you running and what version of MYSQL? see => http://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql (ubuntu tzdata) OR check this protected post => http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc – Louis Loudog Trottier Apr 06 '17 at 22:06
  • Mysql 5.5 you ca use the native function UTC_TIMESTAMP() https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp – Louis Loudog Trottier Apr 06 '17 at 22:10
  • I'm running MySQL 5.6.17 on Windows 8 (Wamp server). Unfortunately, UTC_TIMESTAMP is apparently an "invalid default value". – Jiam30 Apr 06 '17 at 22:38
  • Also, running SET TIME_ZONE = '-00:00'; SELECT CURRENT_TIMESTAMP; gives the right UTC time (that's why I don't understand why the default value is bad). – Jiam30 Apr 06 '17 at 22:39
  • Last thing: SET GLOBAL TIME_ZONE='-00:00'; solves the issue. But I'd like to avoid that (since I have other databases that don't need it on the same server). – Jiam30 Apr 06 '17 at 23:05

0 Answers0