Does mybatis change time zone while inserting into a DB?

Question

While inserting a record into a table in Oracle, the time stamp in the created date column is 4 pm UTC(in the complete format eg...2020-05-20 10:30:20.15 UTC). That is verified even just before calling the save method of Mybatis. After the save method is called, the created date column in the new record in the DB does not have UTC as the time. It shows 4 pm Asia/Calcutta. The numeric part of the time stamp is correct but the timezone got changed.

Our code is in Spring framework - mybatisMapper.save(events) .. the created_date column in events POJO is of java.util.Date type. We are trying to insert a record in DB hosted in Montreal. select dbtimezone from dual; DBtimezone of the sever - -04:00 means American/Montreal time zone(hosted in America/Montreal)

select sessiontimezone from dual; session time zone - Asia/Calcutta.(because the application is running in India).

Data type of the created date column is TIME STAMP WITH TIMEZONE. Is mybatis changing the time zone by any chance or is it dropping it altogether and letting Oracle decide the timezone by itself?

On top of timezone I would check the locale. Can you check the one on your local system and the database host? — Vargan
locale? Database locale? did not know about this. what are all possible causes of this? can you elaborate on how locale might cause the trouble here? — Praveen Nvs
Not every country has a daylight saving time, if wrongly set could cause the issue. I also second Basil Bourque and would get rid of java.util.Date — Vargan
Even after trying OffsetDateTime, the problem persists. WHat could be the reason? — Praveen Nvs
It should work. Just be sure to use the latest versions of the driver and MyBatis. In version < 3.5.0, MyBatis internally used PreparedStatement#setTimestamp() and that caused time zone related issues. Support of Java Time API varies between DBs/drivers. For OffsetDateTime, see here for more info. — ave

Basil Bourque Basil Bourque · Accepted Answer · 2020-05-22T15:08:00

Never use java.util.Date. That terrible class was supplanted years ago by the modern java.time classes defined in JSR 310.

Among the problems with Date is that its toString method dynamically applies the JVM‘s current default time zone while generating its text. This creates the illusion of that zone being contained within the object while actually a Date (despite its misnomer) represents a moment in UTC. One of many reasons to avoid this class.

Since you did not post any code, I can only guess that your problem is that you are being misled by the lie told by Date::toString.

As of JDBC 4.2 we can exchange java.time objects with the database. Specifically, use OffsetDateTime class to exchange values stored in a column of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE. Instant and ZonedDateTime might also work depending on your JDBC driver, but oddly JDBC 4.2 does not require support for those two more commonly used classes. I generally suggest you store an Instant on your class, converting to/from OffsetDateTime.

FYI, the time zone Asia/Calcutta has been renamed Asia/Kolkata.

Example code

String input = "2020-05-20T10:30:20.15Z" ;
Instant instant = Instant.parse( input ) ;
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
ZonedDateTime zdt = instant.atZone( ZoneId.of( "Asia/Kolkata" ) ) ;

Write to database.

…
myPreparedStatement.setObject( … , odt ) ;

Retrieval from database.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( ZoneId.of( "Asia/Kolkata" ) ) ;

This has been covered many many times already on Stack Overflow. Search to learn more.

Does mybatis change time zone while inserting into a DB?

1 Answers