Oracle Function: EXTRACT()

Question: How do I find the hour portion of the value between to timestamps?
Answer: Use the EXTRACT() function!

Thankfully @TheRealLewisC (twitter | blog) pointed us in the right direction! I was tempted to do a subtraction and then a substr() to pull out just the hour portion of the returned string – way more work than necessary for the Optimizer. EXTRACT (Oracle Docs) gives us just what we want!

So there is not an exact example of what @leight0nn (Twitter | Blog) is looking for in the docs – but there is enough to figure out:
“How do I find the hour portion of the value between to timestamps?”

So I have 2 points in time. What’s the delta in hours? There’s 2 ways to see this
1 – What is the hour portion of the A – B result?
2 – What is the total number of hours of the A – B result?

Answer A:
[sql]
SELECT EXTRACT (HOUR FROM (CURRENT_TIMESTAMP – MAX (stuff))) FROM times;
— stuff is a column in table times that contains a timestamp value
— CURRENT_TIMESTAMP is an Oracle function (<a href="http://download.oracle.com/docs/cd/B14156_01/doc/B13812/html/sqfunc.htm#i1007596">docs</a>) that returns the current time
[/sql]

Answer B:
We need to take the number of days, multiply that by 24 and add that to the hours portion.
[sql]
SELECT (EXTRACT (DAY FROM (MAX (stuff) – MIN (stuff))) * 24)
+ EXTRACT (HOUR FROM (CURRENT_TIMESTAMP – MAX (stuff))) TOTAL_HOURS
FROM times;
— You’ll get an ORA-30076 if you try to extract a portion that’s not there
— For example ask for YEAR when the time does not contain a YEAR component
[/sql]