JDBC and oracle database timezone handling

Question

I have created sample table in oracle DB as below

"CREATED_ON" TIMESTAMP (6), 
"CREATED_ON_TIMEZONE" TIMESTAMP (6) WITH TIME ZONE, 
"TIMEZONE_GMT" TIMESTAMP (6) WITH TIME ZONE

and inserted values from java as below

preparedStatement.setTimestamp(1, new Timestamp(new Date().getTime()));
preparedStatement.setTimestamp(2, new Timestamp(new Date().getTime()));
preparedStatement.setTimestamp(3, new Timestamp(new Date().getTime()) ,Calendar.getInstance(TimeZone.getTimeZone("UTC")));

JVM timezone in ASIA/CALCUTTA. I have used SQL developer to query data. I just wanted to clear my understanding

The first column stored value as per local JVM without timezone since dataType is only timestamp i.e 29-NOV-17 07.04.28.014000000 PM. so for column with timstamp datatype DB stores value as of local JVM which is passed by JDBC driver and there is no conversion happening either JDBC side or DB side ?
Second column store value with TIMEZONE i.e 29-NOV-17 07.04.28.014000000 PM ASIA/CALCUTTA. So does it mean DB stores value for column with timezone information provided by JDBC driver and there is no convrsion at DB side?
I want to store value in GMT so I set third parameter as GMT , it store value in GMT but timezone was still showing as of local JVM . i.e 29-NOV-17 01.34.28.014000000 PM ASIA/CALCUTTA

I was refering below article but my observations looks totally diffrent. http://brian.pontarelli.com/2011/08/16/database-handling-for-timezones/

Are these all different values? If not, i.e. if they represent the same time I would propose a virtual column as TIMEZONE_GMT TIMESTAMP(6) GENERATED ALWAYS AS ( SYS_EXTRACT_UTC(CREATED_ON_TIMEZONE) ) VIRTUAL resp. CREATED_ON TIMESTAMP(6) GENERATED ALWAYS AS ( CAST(CREATED_ON_TIMEZONE AS TIMESTAMP(6)) ) VIRTUAL — Wernfried Domscheit
Updated question . These are same values. The main issue is with third column , it shows correct GMT value but timezone is different . It should be GMT. — user530158
You need to pass instances of oracle.sql.TIMESTAMPTZ through setObject() if you want to preserve the time zone. — a_horse_with_no_name
Can’t you use Java 8 and java.time? If you can, skip the outdated Timestamp class. I believe you should save LocalDateTime objects into your timestamp column and Instant objects into your timestamp with timezone columns. In both cases use PreparedStatement.setObject(). java.time is so much nicer to work with. — Ole V.V.

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2017-11-29T14:54:17

Problem is Java Timestamp does not contain any time zone information.

So you insert a TIMESTAMP value into a column of TIMESTAMP WITH TIME ZONE. In such case Oracle makes an implicit cast with FROM_TZ:

FROM_TZ(<your value>, SESSIONTIMEZONE)

Command preparedStatement.setTimestamp(3, new Timestamp(new Date().getTime()) ,Calendar.getInstance(TimeZone.getTimeZone("UTC"))); would be correct only after an ALTER SESSION SET TIME_ZONE = 'UTC';

JDBC and oracle database timezone handling

1 Answers