Oracle query between two timestamps not returning records

Question

I am trying to get all records in the database that have an update date between the interval of systimestamp -15 minutes, to the current systimestamp.

So what I do is:

and (CAST (update_date AS TIMESTAMP)  at time zone 'UTC') BETWEEN  (cast((systimestamp - interval '15' minute) at time zone 'UTC' as timestamp)) AND  (cast((systimestamp) at time zone 'UTC' as timestamp))

If I take them separate for a specific record:

(CAST (update_date AS TIMESTAMP)  at time zone 'UTC') -> 26-APR-18 01.10.16.000000000 AM UTC
(cast((systimestamp - interval '15' minute) at time zone 'UTC' as timestamp)) -> 26-APR-18 12.57.04.136000000 AM
(cast((systimestamp) at time zone 'UTC' as timestamp)) -> 26-APR-18 01.12.04.136000000 AM

Basically the first one is between the two other ones, so the query should return a record, but it doesn't. Any help?

Answer 1

SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Assuming update_date is also a TIMESTAMP WITH TIME ZONE you don't need any cast at all, simply run

update_date BETWEEN SYSTIMESTAMP - interval '15' minute AND SYSTIMESTAMP

Comparisons are always done internally in UTC, see Datetime and Interval Arithmetic:

Oracle Database performs all timestamp arithmetic in UTC time. For TIMESTAMP WITH LOCAL TIME ZONE data, Oracle Database converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME ZONE data, the datetime value is always in UTC, so no conversion is necessary.

As you have DATE values in PST (nb, how do you handle daylight-saving-times in this case?) you would run

FROM_TZ(CAST(update_date AS TIMESTAMP), 'PST') BETWEEN SYSTIMESTAMP - interval '15' minute AND SYSTIMESTAMP