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?
[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
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
— 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
And Lewis 🙂