CODEWITHSUNDEEP
javamysqlspring-boot
JXpaRroW
JXpaRroW

Reputation: 3

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

I've a Ubuntu 16 server in "IST" TZ.
MySQL (v5.7) server in "IST" TZ.
Spring-boot (v2), Java (v1.8) jar in "America/Los_Angeles" TZ. Setting from -Duser.timezone arg.

Problem: As my Java Date obj is in "America/Los_Angeles" TZ, fetching MySQL data was not working as MySQL date was a day behind from java date.
Found the above difference in Java when logged actual date obj (query parameter obj to be used) without passing it to query and the java object fetched from MySQL after select *.

Solution: Added "spring.jpa.properties.hibernate.jdbc.time_zone = America/Los_Angeles" in application.properties. It worked fine as expected.

Questions:
1. 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?
2. In MySQL DB, can I check each connection's timezone? Similar like in, I can see the connection list from "show processlist" query.

Upvotes: 0

Views: 988

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338171

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

Table of date-time types in Java (both legacy and modern) and in standard SQL

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?

Upvotes: 0

Related Questions