Skip to content
Advertisement

Convert Timestamp to minutes in Oracle after a subtraction

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement