0

I have two services. Here is some info which is true for both the services. Language:Java ORM: mybatis - they interact with DB using this framework. DB: Oracle.

Aim: Insert time in 'created_date' column in DB in UTC timezone.

In both services, Timezone.setDefault(Timezone.get("UTC")) is set. Apart from this line, we are setting timezones nowhere in the services. This line sets the JVM default timezone. Then, we try to insert in the DB using mybatis.save(record). Created_date has java.util.Date as the type in both.The column in the DB has TIMESTAMP WITH TIMEZONE for both. In service 1, it works.. if the time sent is 3 pm UTC, the created_date column in the DB shows 3 pm UTC. But, In service 2, for the same time, it is 3 pm Asia/Calcutta. Notice the change in timezone. the numeric part is correct though.

For both DBs, DBtimezone = America/Montreal, sessiontimeZone = Asia/Calcutta.

I know that using java.util.Date is nasty when dealing with timezones and we have to use java.time-classes but I am curious to to know the reason for this inconsistent behaviour.

And the behaviour in service 3(another service whose dbtimezone is UTC and sessiontimezone is Asia/Calcutta) muddies the water further. In service 3 - I am trying to insert 3 pm Australia/Perth(AWST) but in the DB, it comes as 3 pm (UTC). It should have taken either the time given by me(UTC) or the user(session timezone which is Asia/Calcutta) but it took UTC here. UTC is neither JVM timezone nor the user or session timezone.

Looks like frameworks like Hibernate and Mybatis delegate to JDBC to determine the timezone.

This leads us to - Statement: "If we do not compel the framework or JDBC to take a timezone, it will always take JVM timezone as the default timezone."

  1. Is this statement true always?
  2. If you answered true to the above question, service 3 seems to be contradicting this. How do you explain it?
  3. Does it depend on the implementation of the JDBC driver to determine the timezone which means JVM timezone may not always prevail?

Note: 1st and 3rd questions are YES/NO questions. It will be great if you start your answer with YES/NO and then jump into details.

tgallei
  • 827
  • 3
  • 13
  • 22
Praveen Nvs
  • 331
  • 3
  • 14
  • Likely MyBatis is using `setTimestamp(.., )` and not `setObject(.., )` or `setTimestamp(.., , )`. And as required by the JDBC specification, this means the timestamp will be using the JVM default time zone. However, your question is in my opinion pretty hard to read, and would be better served by including a [mre]. – Mark Rotteveel May 28 '20 at 13:54
  • Related: https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific – Mark Rotteveel May 28 '20 at 13:55

0 Answers0