I’m trying to get the minute value from a Timestamp after a subtraction
First I made a subtraction:
Current Time – Target Time(INSP_FIN_DT)
( TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') - TO_TIMESTAMP(INSP_FIN_DT,'YYYY-MM-DD HH24:MI:SS') ) AS REST_TIME
the output:
+00 00:09:44.000000
What I need:
09
I already tried
SELECT SUBSTR(REST_TIME, 7,2)
But the return is ever 00, even If I convert it to Char like:
SELECT SUBSTR(TO_CHAR(REST_TIME),7,2)
How can I solve it by query?
Advertisement
Answer
For my current implementation, I’ll never have more than a hour, in this case I used the bellow solution:
SYSTIMESTAMP - TO_TIMESTAMP(INSP_FIN_DT,'YYYY-MM-DD HH24:MI:SS') AS REST_TIME
Select..
EXTRACT(MINUTE FROM REST_TIME) AS REST_MINUTES
In case of more than 60 minutes, I should use:
(EXTRACT(HOUR FROM REST_TIME) * 60) + EXTRACT(MINUTE FROM REST_TIME) AS REST_MINUTES