Does JDBC (in Spring Data JPA) does not always take JVM default time zone while inserting a record into Oracle DB?

Question

At the start of my application,I have set default timezone as Australia/Perth using Timezone.setDefaultTimezone() method in java. I am trying to insert a time stamp with Australia timezone in created_date column. When the save method of Spring data JPA is called, the time stamp is 4 pm Australia time zone. But, inside DB , it is stored as 4 pm UTC. Our Spring config does not force JDBC to use UTC timezone. No such config was specified. In fact,UTC is not there anywhere in the repository at all.

Problem: I am aware that JDBC driver decides the timezone. Q) Should n't it always take JVM default timezone which in this case is Australia? Q) No matter what timezone I set in the code(either as default timezone or in the date column), it is always UTC in the DB. The numeric part of the timestamp is going correctly though. By any chance, is the JDBC taking DBtimezone instead of JVM's default timezone? Q) Which timezone does JDBC take - JVM 's default or 'DBtimezone' of the DB(in this case,it happens to be the latter)? Or it can be any?

Info: Dbtimezone of DB - select dbtimezone from dual - UTC Sessiontimezone of DB - Asia/Calcutta Created_date column is of type 'TIMESTAMP WITH TIMEZONE'

Note: I tried with java.time classes.. OffsetdateTime and ZonedDateTime but with no success.

Basil Bourque Basil Bourque · Accepted Answer · 2020-05-24T06:35:58

Never set the default time zone. Doing so immediately affects all other code running in all threads of all apps running within that JVM. And any other code can reset it again.

So never write code that relies on the JVM’s current default default time zone. Specify your desired/expected time zone explicitly.

I am trying to insert a time stamp with Australia timezone in created_date column.

(A) Be certain your database column is of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE.

(B) Use only modern java.time types.

(C) Use a JDBC driver compliant with JDBC 4.2 and above.

I do not use JPA. But I can show you straight JDBC,

ZoneId z = ZoneId.of( "Australia/Perth" ) ;
ZonedDateTime zdt = ZonedDateTime.now( z ) ;
OffsetDateTime odt = zdt.toOffsetDateTime() ;
myPreparedStatement.setObject( … , odt ) ;

You may be getting confused by tools and middleware having an unfortunate anti-feature of dynamically applying a default time zone after retrieving the value from the database but before displaying to you. This creates the illusion of a time zone that is not really there. Try retrieving your value from DB using Java:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

You’ll have no time zone issues with the code shown on this Answer. In my experience with JDBC drivers for Postgres and H2, there is no interplay with any default time zones on server or client. Stick to (a) using java.time classes, and (b) always specify your desired/expected time zone rather than rely implicitly on a default zone. I do not use Spring or Hibernate, so I do not know if they might be injecting a time zone along the way.

Try some straight JDBC to eliminate Spring and JPA/Hibernate from the mix, while debugging your situation.

By the way, the time zone Asia/Calcutta has been renamed Asia/Kolkata. Make sure the tz data within your operating systems, database engines, and Java implementations are all up-to-date.

Does JDBC (in Spring Data JPA) does not always take JVM default time zone while inserting a record into Oracle DB?

1 Answers