CODEWITHSUNDEEP
javasqloracle-databasejdbctimezone
AlikElzin-kilaka
AlikElzin-kilaka

Reputation: 36051

Which oracle date data type should I choose when using JDBC on multi-timezone servers?

I have an Oracle DB that has a different timezone that the servers' timezones. The servers themselves have different timezones among themselves.

I tried several datatypes: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, all having different issues, when being used with JDBC. See issues below (my server is in IDT timezone and the DB is on PST timezone).

Which is the correct date data type when on multi-timezone architecture?

To create the table with the differernt date types:

create table ALIK_TZ (
  D date default sysdate,
  t timestamp default systimestamp,
  tz timestamp with time zone default systimestamp,
  ltz timestamp with local time zone default systimestamp
);

Insert value:

insert into ALIK_TZ (d) values (default);

Query:

select to_char(d, 'DD-MON-YYYY HH24:MI:SS') D_SECS, ALIK_TZ.* from ALIK_TZ;

Result:

"D_SECS"     "D"        "T"         "TZ"                            "LTZ"
"01-JUN-2016 06:14:04"  01-JUN-16   01-JUN-16 06.14.04.920000000 AM 01-JUN-16 06.14.04.920000000 AM -07:00  01-JUN-16 04.14.04.920000000 PM
"01-JUN-2016 06:20:54"  01-JUN-16   01-JUN-16 06.20.54.181000000 AM 01-JUN-16 06.20.54.181000000 AM -07:00  01-JUN-16 04.20.54.181000000 PM

Query using JDBC:

preparedStatement = conn.prepareStatement("select * from ALIK_TZ");
resultSet = preparedStatement.executeQuery();

Results for getObject:

D->java.sql.Timestamp(01-JUN-2016 06:14:04)
T->java.sql.Timestamp(2016-06-01 06:14:04.92)
TZ->oracle.sql.TIMESTAMPTZ
LTZ->oracle.sql.TIMESTAMPTZ

As you can see, I can't use D nor T, because I need the result in my timezone.

Regarding TZ, if I use ResultSet.getTimestamp(4), it returns java.sql.Timestamp(2016-06-01 16:14:04.92) - in the correct timezone!!! But the disadvantage is that I cannot use an index on TIMESTAMP WITH TIME ZONE.

Regarding LTZ, I cannot even use ResultSet.getTimestamp(). I need to pass a Calendar. Doing resultSet.getTimestamp(5, Calendar.getInstance()) returns java.sql.Timestamp(2016-06-01 13:14:04.92) - wrong timezone (UTC instead of IDT).

So it seems that I can't find the perfect date-time data type to be used with JDBC. Ideas?

* BTW, using longs (millis from 1970 UTC) can be used but really cumbersome when viewing the data in the DB.

Upvotes: 1

Views: 456

Answers (1)

Philippe Marschall
Philippe Marschall

Reputation: 4604

My personal recommendation would be to use TIMESTAMP WITH TIME ZONE on the database side and java.time.ZonedDateTime on the Java side. This makes you independent from any server and client settings and allows to to convert to any time zone later if you decide to do so.

The only issue is when you're computing and storing dates in the future and the time zone rules for the specific time zone change. However this can be corrected if you detect this early enough.

Upvotes: 0

Related Questions