CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Ankit
Ankit

Reputation: 129

EXTRACT Hour Function is not working as expected

I have a select query where i take difference between two timestamps 

select EXTRACT (HOUR FROM (systimestamp-queuereceiveddate(a,b))) 
  from dual

The queuereceived date function also returns a timestamp. I am able to extract days by using the extract function but not hours.

Please advise on this

Upvotes: 0

Views: 518

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Extracting the hour certainly works:

select systimestamp - (systimestamp - 27.5/24) as diff,
    extract(day from systimestamp - (systimestamp - 27.5/24)) as diff_day,
    extract(hour from systimestamp - (systimestamp - 27.5/24)) as diff_hour
from dual;

DIFF                             DIFF_DAY  DIFF_HOUR
------------------------------ ---------- ----------
+000000001 03:30:00.584929              1          3

If you're expecting it to show the total number of hours, if the difference is more than one day, then you misunderstand what the extract function is doing, and you'll need to calculate a total by multiplying the day value by 24 and adding that to the hour value:

select systimestamp - (systimestamp - 27.5/24) as diff,
    extract(day from systimestamp - (systimestamp - 27.5/24))  * 24
        + extract(hour from systimestamp - (systimestamp - 27.5/24))
        as diff_hour_total
from dual;

DIFF                           DIFF_HOUR_TOTAL
------------------------------ ---------------
+000000001 03:30:00.612830                  27

Of course I'm guessing since you haven't explained the problem you're seeing, but since the function does work this is the only thing I can think you mean...

Upvotes: 3

Related Questions