Hi,
Using Oracle 11g R2.
I need to develop a query which displays records that exceed a certain time duration. I've determined (with Frank's help) that I need to add a 2 hour interval to my query in order to obtain correct results, but I'm having a hard time understanding why this 2 hour interval must be added to the query. The table structure and query are simple and are as follows.
create table testtime (col1 number, col2 timestamp(6))
insert into testtime values (1, systimestamp)
insert into testtime values (2, systimestamp)
select col1
where systimestamp + interval '2' hour - col2 >= numtodsinterval(5, 'SECOND')
If I run the following query, you can see that the difference between systimestamp and the col2 values where were inserted seconds ago shows an accurate time difference.
select systimestamp
+ interval '2' hour
- col2
from testtime
+00 00:00:10.279795
+00 00:00:08.931349
Can someone please help me understand why I need to add this interval '2' hour statement? Note that I'm in Arizona and the systimestamp shows 2 hours ahead (EST).