6

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.

karol
  • 380
  • 1
  • 5
  • 16
J Freebird
  • 3,664
  • 7
  • 46
  • 81
  • A Timestamp is always in UTC. If you want a different Timezone, you have to convert it yourself by using `ZonedDateTime`. – Guillaume F. Feb 13 '18 at 02:04
  • 2
    Is your [Oracle column data type](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1838) `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, or `TIMESTAMP WITH LOCAL TIME ZONE`? Each is *very* different. – Basil Bourque Feb 13 '18 at 02:16
  • Probably duplicate with https://stackoverflow.com/questions/1289492/hibernate-timestamp-with-timezone – Bejond Feb 13 '18 at 02:20
  • @BasilBourque it's TIMESTAMP(3); – J Freebird Feb 13 '18 at 02:49
  • @JFreebird Clarifications should be posted as edits to the Question rather than as Comment. – Basil Bourque Feb 13 '18 at 03:14

3 Answers3

4

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.

Babl
  • 7,446
  • 26
  • 37
  • Is there a way to let JDBC respect DB's timezone when retrieving the Date column? Say if it's in PST, then JDBC should also use PST to bind it to Java object. Suppose the inserts come from a different flow. I just don't want to hard code the JDBC timezone. – J Freebird Feb 23 '18 at 01:23
  • 1
    @JFreebird The JDBC specification does not have anything like that specified, but maybe some of the JDBC drivers can do that. But seems Oracle JDBC cannot do it, as basically it is not always required that the client which connects to the server should have access to server configuration data like Timezone. Still, maybe I'm wrong. But usually, if DB server is going to be used from multiple timezones it always uses the UTC timezone. That is some kind of a "Best Practice". – Babl Feb 23 '18 at 07:30
0

The problem is with Oracle column data type you are using, If you go to official oracle docs, link, you will notice the TIMESTAMP doesn't respect timezone, so you should go with with either TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

enter image description here

Arjun
  • 3,248
  • 18
  • 35
  • So what time zone does TIMESTAMP use? Currently in our Oracle db it's using PST. I think it has something to do with Hibernate as well. Hibernate is converting the selected date into Java Date object using the JVM's timezone. – J Freebird Feb 16 '18 at 19:03
0

It seems the issue is with few concepts of date handling

  • Dates in DB should be in UTC, if they are to be used in multiple timezone.
  • Java program should convert the dates to required timezone.

If you take your date in database as based on UTC, and then check the output of your hibernate query, you should see that date is changed as per JVM's timezone.

If you want the same date back (as was in DB), maybe you should convert the date to UTC timezone.

Now, you can do either of following:

  • Set the JVM's timezone same as the database record timezone. Not recommended
  • Change your dates in database as per UTC, and let the dates be changed to JVM's timezone. Recommended
Aakash
  • 2,029
  • 14
  • 22