Friday, December 20, 2019

Time Interval

Simple example of converting time-time to hrs/minutes. Change the times or date in the subquery to see what happens.
SELECT start_tm                                                       AS start_time
     , end_tm                                                         AS end_time
     , NUMTODSINTERVAL(end_tm - start_tm, 'DAY')                      AS NUMTODSINTERVAL
     , SUBSTR(NUMTODSINTERVAL(end_tm - start_tm, 'DAY'), 12, 5)       AS substr
     , EXTRACT(hour FROM NUMTODSINTERVAL(end_tm - start_tm, 'DAY'))   AS hour
     , EXTRACT(minute FROM NUMTODSINTERVAL(end_tm - start_tm, 'DAY')) AS min 
  FROM (
        SELECT TO_DATE('1/21/2015 9:00', 'mm/dd/yyyy hh24:mi')  AS start_tm 
             , TO_DATE('1/21/2015 19:45', 'mm/dd/yyyy hh24:mi') AS end_tm 
          FROM dual
       ); 

No comments:

Post a Comment