CODEWITHSUNDEEP
sqloracle-databasetimestampintervals
Kairan
Kairan

Reputation: 5542

Oracle SQL Query Where Timestamp is in Last 1 hour Using Systimestamp

Using oracle 11g

I read another SO post where interval '1' hour should work the same way I expect it to, so I am wondering why I would get different results from these two queries and maybe help better understanding how the interval query works...

The intention of these two queries is to get all records where they have been on the table for less than 1 hour.

select err_ts from tbl where err_ts > systimestamp-(1/24) order by err_ts desc;

30-JAN-15 02.14.48.000000000 PM
30-JAN-15 02.08.58.000000000 PM
30-JAN-15 01.49.09.000000000 PM

select err_ts from tbl where (systimestamp - err_ts) < interval '1' hour order by err_ts desc;

30-JAN-15 02.14.48.000000000 PM
30-JAN-15 02.08.58.000000000 PM
30-JAN-15 01.49.09.000000000 PM
30-JAN-15 01.28.40.000000000 PM
30-JAN-15 01.21.12.000000000 PM
30-JAN-15 01.17.06.000000000 PM

Upvotes: 2

Views: 12460

Answers (1)

Kairan
Kairan

Reputation: 5542

Using localtimestamp rather than systimestamp solved the issue as implicitly converting to a date type to subtract (1/24) loses the time zone.

Upvotes: 1

Related Questions