CODEWITHSUNDEEP
javaoracle-databasemybatisspring-mybatistimestamp-with-timezone
Praveen Nvs
Praveen Nvs

Reputation: 351

Does mybatis change time zone while inserting into a DB?

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?

Upvotes: 2

Views: 4914

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338171

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.

Upvotes: 4

Related Questions