CODEWITHSUNDEEP
javaoracle-databasedatetimejdbctimezone
Anish Sharma
Anish Sharma

Reputation: 11

How to ignore DST adjustments made by java while retrieving date column from an Oracle database?

I have an Oracle table with Date column in it.

+--------+-----------------------+
|  ID    | CURR_DATE             |       
+--------+-----------------------+                       
|   1    |2003-04-06 02:59:59    |
+--------+-----------------------+

When I do a 

select * from tablename

in the database console, I get the correct column value for CURR_DATE.

But when I do the same from a java program using JDBC, the time shifts one hour forward due to daylight saving. Here is a link that explains it. https://www.timeanddate.com/time/change/usa/new-york?year=2003.

So I receive the value 2003-04-06 03:59:59 instead of 2003-04-06 02:59:59.

This happens because the DB and the JVM are in PST and PST follows DST.(Pls correct me if I am wrong)

Here is my code

String query="select * from tablename";         
PreparedStatement psmt = con.prepareStatement(query);
ResultSet results=psmt.executeQuery();
while(results.next()) {
    System.out.println(results.getString(2));
}

What can be done so that results.getString() returns me the same value which is in the database(without any adjustments).

I want it to return me the original value 2003-04-06 02:59:59 instead of the adjusted value 2003-04-06 03:59:59.

Upvotes: 1

Views: 827

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 339462

tl;dr

java.time.LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

Use smart objects, not dumb strings

You storing a date-time value in a date-time column, so use a date-time type in Java. Currently your are using strings.

The Oracle DATE type holds only a date and a time-of-day without any concept of time zone or offset-from-UTC. As such, a DATE column cannot be used to represent a moment. The equivalent in standard SQL is TIMESTAMP WITHOUT TIME ZONE.

For example, if a row stores a value in that column for the 23rd of January of this year at noon, we have know way to know if this is noon in Tokyo, noon in Kolkata, noon in Paris, or noon in Montréal. These various noons all occur in different moments, hours apart.

The appropriate type in Java to match an Oracle DATE column is LocalDateTime. This class also lacks any concept of time zone or offset-from-UTC. So it cannot be used to represent a moment. But when you have only a date and time-of-day without zone/offset, this is the class for you.

JDBC 4.2 and later requires support for directly exchanging java.time objects with the database. 

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

Writing data.

myPreparedStatemen.setObject( … , ldt ) ;

By exchanging a Java object without any zone/offset with a database column of a types without any zone/offset, you will have no issue with zone/offset. But be aware of the limitations of this kind of date-time value:

  • Not a moment, not a point on the timeline.
  • Ambiguous as it could be interpreted as any of many potential moments along a range of about 26-27 hours (the range of time zones around the globe).

Table of date-time types in Java (both modern and legacy) and in standard SQL.

Upvotes: 1

Philippe Marschall
Philippe Marschall

Reputation: 4604

As @gord-thompson explained you should use an up to date ojdbc8 or ojdbc10, ideally 19.3 or 18.3, you can use these even if your database is 11.2g https://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#01_02

results.getObject(2, LocalDateTime.class)

Explanation

The issue is really with the implementation of the java.sql.Timestamp class. It represents the milliseconds since 1970-01-01 00:00:00 GMT and 2003-04-06 02:59:59 in JVM time zone, 2003-04-06 02:59:59 does not exist in JVM time zone so the parser is off by the DST change.

LocalDateTime is not bound to any time zone and just year-month-day-hour-minute-second-nano and therefore has no issues representing 2003-04-06 02:59:59 whatever the JVM time zone.

Upvotes: 2

Related Questions