How To Calculate Sum Time With Data Type Char In Oracle

i am trying to create a query to sum record with data type char in oracle from 2 table different. I have table like this : table 1: name time_rent ---------------- ---

Solution 1:

WITH x AS (
  SELECT t1.time_rent AS t1,
         t2.time_rent AS t2,
         ((SUBSTR(t1.time_rent,1,2) *3600) + (SUBSTR(t1.time_rent,4,2) *60)
         + (SUBSTR(t2.time_rent,1,2) *3600) + (SUBSTR(t2.time_rent,4,2) *60)) AS t 
   FROM table1 t1 
   INNERJOIN Table2 t2 
   ON t1.name=t2.name
  ),
y AS(
   SELECT t1,
          t2,
          numtodsinterval(t,'second') AS t
          FROM x
)
SELECT T1,
       T2,
       (EXTRACT(dayFROM t) *24+EXTRACT(hourFROM t) ||':'||EXTRACT(minuteFROM t) ||':'||EXTRACT(secondFROM t)) AS duration 
       FROM y

Output

T1      T2      DURATION
07:30   18:30   26:0:0

Live Demo

http://sqlfiddle.com/#!4/aaa519/12

Solution 2:

An important advice to you: Please do not store time intervals as strings ( CHAR/ VARCHAR2 ). It makes operations on them harder to implement while writing queries and performs inefficiently on large data sets.

Oracle provides you with two data types precisely for such interval records in the database, which you wish to store.

INTERVAL

INTERVAL YEAR [(year_precision)] TO MONTH - Stores a period of time in years and months

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)] - Stores a period of time in days, hours, minutes, and seconds

The second type is useful to you to store the required values as INTERVAL .. HOUR to MINUTE.

Eg:- INTERVAL '07:30' HOUR TO MINUTE

So, the sum operation would have been simply, time_rent + time_expired, which could have made it much easier to operate on. Now, since you have stored them as characters, Oracle does provide functions for your rescue :

TO_DSINTERVAL - TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND.

So appending DAY component and seconds component(zeroes) to your time columns will help converting them to INTERVAL types: TO_DSINTERVAL('000 '|| time_rent|| ':00') .

Thus, your final query would look something like.

WITH t
     AS (SELECT t1.name,
                  TO_DSINTERVAL('000 '||    time_rent||':00')
                + TO_DSINTERVAL('000 '|| time_expired||':00') AS intv
         FROM   table1 t1
                join table2 t2
                        ON t1.name = t2.name)
SELECT name,
       EXTRACT(dayFROM intv) *24+EXTRACT(hourFROM intv) --hours||':'||EXTRACT(minuteFROM intv) as duration  -- minutesFROM   t;  

Since the addition operation makes the total duration to exceed 24 hours(26), by default the interval will be considered as 1 day 2 hours. The EXTRACT function is used to get the o/p in the desired HH:MM format. You may use the EXTRACT function to retrieve any component from an interval (day.hour,minute,second ..) and modify the query to display in the desired format.

Demo