MySQL DB server using system timezone while saving timestamp column

Question

We are using - Java 8, Web Server - Tomcat 8.5, DB server - MySQL 5.7, Hibernate -5, Tomcat jdbc pool based data source.

We upgraded MySQL connector J from 5.1.46 version to 8.0.16. Our web server timezone is UTC and mysql server is in IST time zone now before upgrading driver timestamp column value is persisted(using hibenate) in UTC only but after moving to J8 driver it get saved to MySQL server timezone that is IST.

We want timestamp value to be stored/fetch in UTC only.

Gopal Lal Gopal Lal · Accepted Answer · 2018-10-27T07:52:59

By default, the connection time zone is the MySQL database server’s time zone. You can use a different time zone when you connect to MySQL database server. For this, you have to set the session’s time zone to a different time zone like this:

  SET time_zone ='+03:00';

I think, this should work.

Also while creating your tables, you can write queries like that:

  CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

I hope this will work. For more detail about MySql timestamp, please have a look on this nice Article:

http://www.mysqltutorial.org/mysql-timestamp.aspx

MySQL DB server using system timezone while saving timestamp column

1 Answers