CODEWITHSUNDEEP
javaoracle-databasespring-jdbc
Rich
Rich

Reputation: 15767

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.

Upvotes: 2

Views: 1581

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

I think you're seeing two things; when you do your debugging queries the way you are pulling, formatting and displaying the b and c values is causing Java to convert the time zone. But I think that's a red herring, as that won't directly affect your original query to find recently-changed rows. It may be related to your Java locale though, and so be related to the main query issue.

If you trace the query you were originally running, you should see that the filter it actually uses is:

SYS_EXTRACT_UTC(INTERNAL_FUNCTION(RESERVATIONDATE))
  >=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)-NUMTODSINTERVAL(TO_NUMBER(:SECONDS),'SECOND'))

The function calls are there because "During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable."; so the column value is converted to a timestamp with timezone to be compared against systimestamp, and then to compare two timestamp with zone values they are both converted to UTC.

Then this goes back to what @WernfriedDomscheit said. The sys_extract_utc() function takes as its argument a datetime_with_timezone value, so when a plain timestamp is passed in it is implicitly being converted using the SESSIONTIMEZONE. You are seeing different results for your query because the session time zones are different between your Java code and SQL Developer.

You can select sessiontimezone from dual in both to see what those are, or see how timestamps are actually being converted.

You can avoid the problem by casting the system time back to a time-zone-free timestamp:

... where reservationDate >= cast(systimestamp as timestamp) - NUMTODSINTERVAL( :seconds, 'SECOND' );

Tracing that shows the simpler:

RESERVATIONDATE
  >=CAST(SYSTIMESTAMP(6) AS timestamp)-NUMTODSINTERVAL(TO_NUMBER(:SECONDS),'SECOND')

Changing your column definition from timestamp to timestamp with [local] time zone would also work; with local the conversion to UTC still happens, without local it doesn't, according to my traces. This is an interesting read too.

If you look at how the sys_extract_utc() calls are resolved with different session time zones you can see the discrepancy, using a freshly-inserted row and omitting the date elements for brevity:

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

alter session set nls_timestamp_format = 'HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'HH24:MI:SS.FF1 TZR';

alter session set time_zone = 'Europe/London';

select reservationdate a,
  systimestamp b,
  systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND') c,
  cast(reservationdate as timestamp with time zone) d,
  sys_extract_utc(reservationdate) e,
  sys_extract_utc(systimestamp) f,
  sys_extract_utc(systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND')) g
from my_table;

A           B                 C                 D                        E           F           G          
----------- ----------------- ----------------- ------------------------ ----------- ----------- -----------
17:12:13.9  17:12:15.0 +01:00 17:12:10.0 +01:00 17:12:13.9 EUROPE/LONDON 16:12:13.9  16:12:15.0  16:12:10.0 

alter session set time_zone = 'UTC';

select reservationdate a,
  systimestamp(6) b,
  systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND') c,
  cast(reservationdate as timestamp with time zone) d,
  sys_extract_utc(reservationdate) e,
  sys_extract_utc(systimestamp) f,
  sys_extract_utc(systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND')) g
from my_table;

A           B                 C                 D              E           F           G          
----------- ----------------- ----------------- -------------- ----------- ----------- -----------
17:12:13.9  17:12:15.4 +01:00 17:12:10.4 +01:00 17:12:13.9 UTC 17:12:13.9  16:12:15.4  16:12:10.4

My set-up seems to be different to yours - my database server is on UK time, but DBTIMEZONE is +00:00 - but even so, notice the difference between the two values in the e column. In my case it looks like it will find too much data rather than too little - as e >= g is true. In your case I believe that if you run those from SQL Developer and Java you'll see the discrepancy going the other way, because the session time zone from your Java application (based on its locale) is causing the shift the other way.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Not a fully qualified answer but here are some information:

Data type TIMESTAMP does not store any time zone information. When you insert timestamp with time zone (e.g. SYSTIMESTAMP) then time zone information is cut off.

SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE data type in time zone of database servers' operating system time zone (UTC in your case)

Whenever you convert/cast a TIMESTAMP (without time zone) to TIMESTAMP WITH TIME ZONE then Oracle takes SESSIONTIMEZONE into account - unless you explicitly set the time zone with FROM_TZ or similar.

Upvotes: 4

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6299

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

Upvotes: 3

Related Questions