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.

Siva
  • 101
  • 3

2 Answers2

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

Kondybas
  • 4,800
  • 19
  • 16
0

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

Siva
  • 101
  • 3