9 Replies Latest reply on Sep 2, 2014 10:42 AM by Moazzam

# How to calculate time difference

Hi friends,

I want to calculate and sum the time difference.

i have two fields in a table. IN and OUT. for eg.

IN          OUT          DIFFER

01:46     15:30               ?

01:47     15:45               ?

02:50     17:30               ?

I want minus above figure and result will appear in differ column.

Regards,

• ###### 1. Re: How to calculate time difference

What is the data type of the IN and OUT fields? (hint - it's really easy if they are DATE)

• ###### 2. Re: How to calculate time difference

Hi,

Assuming both the columns are of VARCHAR2 datatype and "represent" hours and minutes:

WITH sample_data AS (SELECT '01:46' in_col, '15:30' out_col FROM DUAL

UNION ALL

SELECT '01:47', '15:45' FROM DUAL

UNION ALL

SELECT '02:50', '17:30' FROM DUAL

)

SELECT in_col,

out_col,

to_char(TO_DATE((TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI') differ

FROM sample_data;

SQL> WITH sample_data AS (SELECT '01:46' in_col, '15:30' out_col FROM DUAL

2                       UNION ALL

3                       SELECT '01:47', '15:45' FROM DUAL

4                       UNION ALL

5                       SELECT '02:50', '17:30' FROM DUAL

6                      )

7  SELECT in_col,

8         out_col,

9         to_char(TO_DATE((TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI') differ

10  FROM sample_data;

IN_COL OUT_COL DIFFER

------ ------- ------

01:46  15:30   13:44

01:47  15:45   13:58

02:50  17:30   14:40

SQL>

• ###### 3. Re: How to calculate time difference

and to take it one step further...

WITH sample_data (in_col, out_col)

AS (SELECT '01:46', '15:30' FROM DUAL UNION ALL

SELECT '01:47', '15:45' FROM DUAL UNION ALL

SELECT '02:50', '17:30' FROM DUAL UNION ALL

SELECT '23:30', '01:30' from dual)

SELECT in_col

, out_col

, CASE WHEN out_col < in_col

THEN

TO_CHAR(TO_DATE(((TO_DATE(out_col,'HH24:MI') + 1) - TO_DATE(in_col,'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI')

ELSE

TO_CHAR(TO_DATE((TO_DATE(out_col,'HH24:MI') - TO_DATE(in_col,'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI')

end differ

FROM sample_data;

hth

• ###### 4. Re: How to calculate time difference

Hi Roger,

Fields type are varchar2

Regards

• ###### 5. Re: How to calculate time difference

try this

```SQL> WITH sample_data AS
2  (
3  SELECT '01:46' in_col, '15:30' out_col FROM DUAL
4  UNION ALL
5  SELECT '01:47', '15:45' FROM DUAL
6  UNION ALL
7  SELECT '02:50', '17:30' FROM DUAL
8  )
9  SELECT in_col,
10  out_col,
11  to_char(TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI') + trunc(sysdate),'hh24:mi') differ
12  FROM sample_data;

IN_CO OUT_C DIFFE
----- ----- -----
01:46 15:30 13:44
01:47 15:45 13:58
02:50 17:30 14:40
```
• ###### 6. Re: How to calculate time difference

Hi Nimish,

Yes. Its working. Thanks a lot.

Regards,

• ###### 7. Re: How to calculate time difference

Hi Nimish

can i sum differ?

Regards,

• ###### 8. Re: How to calculate time difference

I'm sure you could but it would depend what you want the output to look like.

Post appropriate example data and expected output so that people can help properly.

• ###### 9. Re: Re: How to calculate time difference

```WITH sample_data AS
(
SELECT '01:46' in_col, '15:30' out_col FROM DUAL
UNION ALL
SELECT '01:47', '15:45' FROM DUAL
UNION ALL
SELECT '02:50', '17:30' FROM DUAL
)
, tmp AS
(
SELECT in_col,
out_col,
to_char(TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI') + trunc(sysdate),'hh24:mi') differ  ,
to_char(TO_DATE(out_col, 'HH24:MI'),'sssss') - to_char(TO_DATE(in_col, 'HH24:MI'),'sssss') differ_sec
FROM sample_data
)
SELECT in_col,
out_col,
differ,
trunc(sum(differ_sec) OVER()/3600) hh,
mod(sum(differ_sec) OVER (),3600)/60 mi
FROM tmp
```

IN_COL OUT_COL DIFFER         HH         MI

------ ------- ------ ---------- ----------

01:46  15:30   13:44          42         22

01:47  15:45   13:58          42         22

02:50  17:30   14:40          42         22

3 rows selected.