Hibernate Ignores DB Timezone Info when Binding to Java Timestamp Object

Question

I have some Dates stored in Oracle with Oracle's TIMESTAMP(3) as its datatype. Now I'm writing a Spring boot app to read those values back. The code is like:

HibernateCallback callback = new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException {
            Query query = session.createSQLQuery("SELECT date_field FROM some_table WHERE some_conditions");

            return query.list();
        }
    };

So:

List results = (List)getHibernateTemplate().execute(callback);
// suppose there's only one row and one column returned
Timestamp ts = result.get(0)[0];

returns me the Java Timestamp object automatically created by Hibernate. The problem is that, when constructing the object, it ignores the timezone stored in Oracle, but instead uses JVM's default timezone. I tested is by setting different timezones for the JVM, and each time it generates a different timestamp.

It's obviously wrong. The Date should be unique on the time line. It shouldn't depend on JVM's timezone. I'm wondering what's the correct way to include the DB's timezone info when parsing the date. Right now it seems it's just using the String representation stored in Oracle and parse it with JMV's timezone. I'm using Hibernate 4.3.4.Final.

PS: The actual query is high customized so I have to write raw SQL.

Answer 1

Basically, that's not even an issue with Hibernate but with JDBC. By default, JDBC Driver will use system time zone on which JVM is running. If you are connecting to the DB server at a different time zone or even if you want to be independent of the current timezone of the system it is a good idea to set JDBC Time Zone explicitly. You can use hibernate.jdbc.time_zone property to set the timezone or do it at runtime via.

session = sessionFactory.withOptions()
.jdbcTimeZone(TimeZone.getTimeZone("UTC"))
.openSession();

Also for Oracle, I would say you can use the TIMESTAMP WITH LOCAL TIME ZONE which will respect you JDBC client time zone.