Rich Rich -4 years ago 49
Java Question

Time zone results differ with systimestamp

I have an Oracle table which contains the following column definition:

COLUMN_NAME : RESERVATIONDATE
DATA_TYPE : TIMESTAMP(6)
NULLABLE : Yes
DATA_DEFAULT: null


Then, from within Java I execute the following command:

insert into my_table (col1, col2, reservationdate) values (:np1, :np2, systimestamp)


Then elsewhere I perform the following command, the aim being to return rows added less than X seconds ago.

select * from my_table where reservationDate >= systimestamp - NUMTODSINTERVAL( :seconds, 'SECOND' )


But no row is returned despite being the reservation date being later than the point identified.

Therefore, I have also run the following command from the same application:

select col1,
col2,
reservationdate,
systimestamp as b,
systimestamp - NUMTODSINTERVAL( 5, 'SECOND' ) as c
from my_table


Which gives the following output:

col1: value1
col2: value2
reservationdate:2017-06-14 14:31:00.746173
b :2017-06-14 15:31:00.905617
c :2017-06-14 15:30:55.905617


Note that the values returned for
b
and
c
are basically one hour ahead of
reservationdate
.

Running the same request on SQL Developer running on the same machine as the Java application gives the correct values:

reservationdate:14-JUN-17 02.31.00.746173000 PM
b :14-JUN-17 02.58.32.863300000 PM +00:00
c :14-JUN-17 02.58.27.863300000 PM +00:00


Oracle is running on one virtual machine, where the output of Unix date is:

Wed Jun 14 14:18:11 UTC 2017


And on the machine where Java is running:

Wed Jun 14 15:21:24 BST 2017


Obviously this is a timezone problem, but I don't see exactly where it is coming from. I'm using
systimestamp
throughout after all, the aim being to do all timestamp calculations on the database server.

My requests pass through Spring's
NamedParameterJdbcTemplate
, and I'm using Oracle Database 11g Express Edition
11.2.0.2.0
.

Answer Source

Try TIMESTAMP WITH TIMEZONE or TIMESTAMP WITH LOCAL TIMEZONE insted of simple TIMEZONE...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download