0

UTC time is showing incorrectly in mysql 5.1. currently, it is 9 AM

mysql> SELECT UTC_TIMESTAMP, UTC_TIMESTAMP( );
+---------------------+---------------------+
| UTC_TIMESTAMP       | UTC_TIMESTAMP( )    |
+---------------------+---------------------+
| 2018-08-01 14:15:03 | 2018-08-01 14:15:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)


SELECT current_timestamp dt1, utc_timestamp dt2, @@global.time_zone tz1, @@session.time_zone tz2;
+---------------------+---------------------+--------+--------+
| dt1                 | dt2                 | tz1    | tz2    |
+---------------------+---------------------+--------+--------+
| 2018-08-01 09:15:56 | 2018-08-01 14:15:56 | SYSTEM | SYSTEM |
+---------------------+---------------------+--------+--------+
1 row in set (0.01 sec)

Please let me know how to fix it.

marked as duplicate by Philᵀᴹ, Md Haidar Ali Khan, Colin 't Hart, hot2use, Erik Darling Aug 2 '18 at 13:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Well, now show OS timezone properties. I think it is UTC-05:00. – Akina Aug 1 '18 at 8:26
  • @Akina CDT -0500 – msp9011 Aug 1 '18 at 8:33
  • If so I cannot understand what is a problem. UTC_TIMESTAMP reports you correct UTC time based on local time and CDT time deviation (minus 5 hours). If it is not correct - your local system timezone is not correct, edit it. – Akina Aug 1 '18 at 9:39
1

mysql need the mysql.timezone* tables to be filled properly for explicit and implicit TZ conversions. They are not created and filled automatically on installation.

  • Install fresh timezones on your host. Usually they are provided by zoneinfo package.
  • Run mysql_tzinfo_to_sql /path/zoneinfo | mysql -u root mysql

Refer the mysql manual for further information: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

0

Finally, found the issue... ntpd service was not running in the server.

Not the answer you're looking for? Browse other questions tagged or ask your own question.