How to Change MySQL Timezone in a Database Connection Using Java

How to change MySQL timezone in a database connection using Java?

useTimezone is an older workaround. MySQL team rewrote the setTimestamp/getTimestamp code fairly recently, but it will only be enabled if you set the connection parameter useLegacyDatetimeCode=false and you're using the latest version of mysql JDBC connector. So for example:

String url =
 "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false

If you download the mysql-connector source code and look at setTimestamp, it's very easy to see what's happening:

If use legacy date time code = false, newSetTimestampInternal(...) is called. Then, if the Calendar passed to newSetTimestampInternal is NULL, your date object is formatted in the database's time zone:

this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
this.tsdf.setTimeZone(this.connection.getServerTimezoneTZ());
timestampString = this.tsdf.format(x);

It's very important that Calendar is null - so make sure you're using:

setTimestamp(int,Timestamp).

... NOT setTimestamp(int,Timestamp,Calendar).

It should be obvious now how this works. If you construct a date: January 5, 2011 3:00 AM in America/Los_Angeles (or whatever time zone you want) using java.util.Calendar and call setTimestamp(1, myDate), then it will take your date, use SimpleDateFormat to format it in the database time zone. So if your DB is in America/New_York, it will construct the String '2011-01-05 6:00:00' to be inserted (since NY is ahead of LA by 3 hours).

To retrieve the date, use getTimestamp(int) (without the Calendar). Once again it will use the database time zone to build a date.

Note: The webserver time zone is completely irrelevant now! If you don't set useLegacyDatetimecode to false, the webserver time zone is used for formatting - adding lots of confusion.

---

Note:

It's possible MySQL my complain that the server time zone is ambiguous. For example, if your database is set to use EST, there might be several possible EST time zones in Java, so you can clarify this for mysql-connector by telling it exactly what the database time zone is:

String url =
 "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false&serverTimezone=America/New_York";

You only need to do this if it complains.

How can I tell mysql the timezone throught connection string?

this is an example of a connection string specifying serverTimezone:

"jdbc:mysql://localhost:3306/returnit?useSSL=false&useUnicode=true&serverTimezone=UTC"

How to set correct MySQL JDBC timezone in Spring Boot configuration

Thanks for your answers, but I have found the solution.

As I suspected, Hikari ignores whatever you put in the datasource URL (so sorry guys, it doesn't matter what you chuck in there), essentially, it reads the timezone setting from MySQL itself, i.e., whatever the result you see when issuing the command:

SELECT @@GLOBAL.time_zone;

in MySQL. In my case, the result was "SYSTEM", which is whatever my local machine it set at. This was AEDT, which is not supported by the MySQL driver and hence my exception.

Running this same query in AWS yielded the value "UTC", which is supported (and, actually what I wanted).

Therefore, I had to set the timezone in my local MySQL server.

Firstly, I had to load the available timezones from my host (Mac OS X) into MySQL. I had to find out where the zoneinfo file was (/usr/share/zoneinfo in my case) then find out out where the `mysql_tzinfo_to_sql' utility was (bin directory of the MySQL installation) and use it to load my local machine's supported timezones. In Mac OS X, I ended up running the command:

/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Then in MySQL I could run the command:

SET GLOBAL time_zone = UTC;

this is a valid timezone, and is synchronized with the cloud based instances.

I think this is a real trap for a lot of people using MySQL with Spring Boot. It will work while people are in supported timezones, but if your development machine should switch to an unsupported timezone, it will quite mysteriously break, I'm surprised that it isn't documented anywhere. The source code of the MySQL Connector/J makes it obvious, but you wouldn't know it otherwise.

Maybe its because MySQL is just so 5 years ago, and I'm an old fossil and, and, well, just get off my lawn!

MySQL JDBC Driver 5.1.33 - Time Zone Issue

Apparently, to get version 5.1.33 of MySQL JDBC driver to work with UTC time zone, one has to specify the serverTimezone explicitly in the connection string.

jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Set MySql time zone dynamically with JVM -Duser.timezone in Spring Boot

tl;dr

Use java.time classes to avoid your time zone issues.

ZoneId z = ZoneId.of( "America/Los_Angeles" ) ;
myPreparedStatament.setObject( … , java.time.OffsetDateTime.now( z ) ) ;   // Persisting
OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;   // Retrieval
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;                           // Adjust into the wall-clock time used by the people of a particular region (a time zone). Same moment, same point on the timeline.

java.time

my Java Date obj

The Date classes (java.util.Date & java.sql.Date) are both terrible, now legacy, supplanted years ago by the modern java.time classes defined in JSR 310.

I've a Ubuntu 16 server in "IST" TZ.

FYI, servers are generally best set to a default time zone of UTC (GMT), an offset of zero hours-minutes-seconds. Be aware that the host OS has its own default time zone, and the JVM has its own default. And middleware such as interactive session with your database may have its own current default time zone.

As a Java programmer, you should never rely on the current default time zone. Any code in any thread of any app within the JVM can change the default at any moment during runtime. So, better to specify the expected/desired time zone.

"spring.jpa.properties.hibernate.jdbc.time_zone = America/Los_Angeles"

If you exchange java.time classes with your database rather than using strings or the legacy date-time classes, you will not need this setting. JDBC 4.2, later versions of Hibernate, and JPA 2.2 all require support for java.time objects.

Is there any way that i can align MySQL TZ with the spring boot Java application TZ, passed with "-Duser.timezone" arg dynamically, i.e, by picking the value from the argument?

If you exchange java.time objects with the database, this question becomes moot, the problem goes away, and your life gets easier.

In MySQL DB, can I check each connection's timezone? Similar like in, I can see the connection list from "show processlist" query.

Ditto, use java.time objects and the question becomes moot.

Example code

Date only, without time-of-day, without time zone

For a column type akin to the SQL standard DATE, use java.time.LocalDate.

ZoneId z = ZoneId.of( "Africa/Casablanca" ) ;
myPreparedStatament.setObject( … , LocalDate.now( z ) ) ;      // Persisting
LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;  // Retrieval

Moment, a point on the timeline

For a column type akin to the SQL standard TIMESTAMP WITH TIME ZONE, use java.time.OffsetDateTime (optionally Instant or ZonedDateTime).

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
myPreparedStatament.setObject( … , java.time.OffsetDateTime.now( z ) ) ;      // Persisting
OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;  // Retrieval

Not a moment, just date and time-of-day

For a column type akin to the SQL standard TIMESTAMP WITHOUT TIME ZONE, use java.time.LocalDateTime. Be aware: Lacking any concept of time zone or offset-from-UTC means this data type cannot represent a moment, is not a point on the timeline.

myPreparedStatament.setObject( … , LocalDateTime.of( 2020 , Month.MAY , 15 , 22 , 0 , 0 , 0 ) ;  // Persisting
LocalDateTime let = myResultSet.getObject( … , LocalDateTime.class ) ;                           // Retrieval

---

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  
  
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

timezone issue while connecting with mysql via spring boot, whats equivalent of Mitteleuropäische Zeit

As seen in this question, you should use IANA timezone formats, like Europe/Berlin:

db2.datasource.url=jdbc:mysql://www<><>06/esystem?useSSL=false&zeroDateTimeBehavior=convertToNull&useUnicode=trueuseLegacyDatetimeCode=false&serverTimezone=Europe/Berlin

How to fix error with MySQL because of timezone change?

This can be fixed on the server-side with an explicit timezone setting in my.cnf. If your timezone is for instance, Europe/Berlin:

[mysqld]
default-time-zone = Europe/Berlin

Does setting the property serverTimezone in jdbcUrl connection url affects read/write of timestamp value from mysql database?

If I set property serverTimezone=UTC in jdbcUrl connection url, does
it affect the timestamp value being read from mysql database?

Yes, it does. Instead of my explanation, I am quoting below the excerpt from https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html:

The server's time zone is overridden by setting the Connector/J
connection property serverTimezone (for example,
serverTimezone=Europe/Paris).

Recommendation: I recommend you use serverTimezone=UTC and then convert the retrieved timestamp to the required time-zone in your application code. Again, to support this recommendation, I am quoting below the excerpt from https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitations.html:

When Connector/J retrieves timestamps for a daylight saving time (DST)
switch day using the getTimeStamp() method on the result set, some of
the returned values might be wrong. The errors can be avoided by using
the following connection options when connecting to a database:

    serverTimezone=UTC

---

Related Topics