Oracle timestamp and Interval

Question

I have a requirement to get records from a table that are systimestamp > LAST_UPDATE_TS + 5 minutes interval

Could you please help with query?

ORACLE DB - 11G Version.

I have tried this as below but not working as expected.

SYSTIMESTAMP   : 11-MAR-20 06.06.00.070695 AM -05:00
LAST_UPDATE_TS : 11-MAR-20 06.05.50.781167 AM

After applying this condition, systimestamp > LAST_UPDATE_TS + INTERVAL '5' MINUTE, I expect no data should return, but still i get rows that doesn't satisfy condition.

Answer 1

You are comparing SYSTIMESTAMP which is a TIMESTAMP WITH TIME ZONE data type to a TIMESTAMP(6) data type; this requires a conversion to a time zone.

If you use:

SELECT SESSIONTIMEZONE FROM DUAL;

The you can see the time zone your session is using.

On db<>fiddle, the default is UTC (+00:00) and running:

SELECT DUMMY AS query1
FROM   DUAL
WHERE  TIMESTAMP '2020-03-11 06:06:00.070695 -05:00' > TIMESTAMP '2020-03-11 06:05:50.781167' + INTERVAL '5' MINUTE

Outputs:

| QUERY1 |
| :----- |
| X      |

Since 2020-03-11 06:06:00.070695 -05:00 is greater than 2020-03-11 06:10:50.781167 +00:00.

If you change the session time zone:

ALTER SESSION SET TIME_ZONE = '-05:00';

and run the same query again (a different column alias was used to prevent caching):

SELECT DUMMY AS query2
FROM   DUAL
WHERE  TIMESTAMP '2020-03-11 06:06:00.070695 -05:00' > TIMESTAMP '2020-03-11 06:05:50.781167' + INTERVAL '5' MINUTE

Then the output has zero rows:

| QUERY2 |
| :----- |

If you want to manually set the timezone in the conversion then you can use the FROM_TZ function:

SELECT DUMMY AS query3
FROM   DUAL
WHERE  TIMESTAMP '2020-03-11 06:06:00.070695 -05:00' > FROM_TZ( TIMESTAMP '2020-03-11 06:05:50.781167', '-05:00' ) + INTERVAL '5' MINUTE

Which, again, outputs zero rows:

| QUERY3 |
| :----- |

db<>fiddle here

Answer 2

SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE value which is compared with a TIMESTAMP value.

Actually Oracle is doing this:

SYSTIMESTAMP > FROM_TZ(LAST_UPDATE_TS + INTERVAL '5' MINUTE, SESSIONTIMEZONE)

Comparison itself is performed on UTC times.

SYSTIMESTAMP is returned in the time zone of database server's operating system. If this time zone is equal to your current SESSIONTIMEZONE then the condition works as expected.

Either change your session time zone to the time zone of database server's operating system or try this one:

LOCALTIMESTAMP > LAST_UPDATE_TS + INTERVAL '5' MINUTE

which does not utilize time zones at all.

Answer 3

After checking few forums and other stackoverflow pages, I tried this and it seams to be working now.

select *
from table
where cast(systimestamp as TIMESTAMP)  > (lst_updt_ts  + interval '3' minute);

Answer 4

Doing it the old way?

systimestamp -5/(60*24) > LAST_UPDATE_TS