CODEWITHSUNDEEP
sqloracle-database
HudsonBarroso
HudsonBarroso

Reputation: 535

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?

Upvotes: 1

Views: 1904

Answers (2)

HudsonBarroso
HudsonBarroso

Reputation: 535

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

Upvotes: 1

Joe Thor
Joe Thor

Reputation: 1260

Information here https://dba.stackexchange.com/questions/53924/how-do-i-get-the-difference-in-minutes-from-2-timestamp-columns

select
  round(
    (SYSDATE - cast(<other_timestamp> as date))
    * 24 * 60
  ) as diff_minutes
from <some_table>;

Upvotes: 1

Related Questions