CODEWITHSUNDEEP
sqloracle-database
Haitham Salah
Haitham Salah

Reputation: 107

The difference between two dates in the same column in oracle in days/Hours/minutes

I am looking to calculate the difference between two dates in days/hours/minutes.

I have a table with the following data structure:

 ID                   Date                           Location          Type
---------------------------------------------------------------------------------
42ABC          15-NOV-14 12.45.00 PM                    YY            Departed 
42ABC          15-NOV-14 03.10.00 PM                    AA            Arrived
42ABC          18-NOV-14 05.15.00 PM                    AA            Departed
42ABC          18-NOV-14 07.20.00 PM                    YY            Arrived

How do i calculate the difference in dates, and get something like this: 

ID                Location                   DURATION
-----------------------------------------------------------------
42ABC               AA                 3 days, 2 hours, 5 minutes

I appreciate your input in this.

Upvotes: 2

Views: 2741

Answers (2)

Allan
Allan

Reputation: 17429

The first solution that comes to mind is:

SELECT id,
       location,
          TRUNC (date_diff)
       || ' days, '
       || TRUNC ( (date_diff - TRUNC (date_diff)) * 24)
       || ' hours, '
       || MOD ( (date_diff - TRUNC (date_diff)) * 24, 10) * 60
       || ' minutes'
FROM   (SELECT   id, location, MAX (date) - MIN (date) AS date_diff
        FROM     your_table
        GROUP BY id, location)

When you subtract two dates in Oracle, the result is a decimal number representing the number of days, so it's just math from there to come up with hours and minutes. If you want this to be more sophisticated (e.g. removing sections if the number is zero), then I'd recommend using a function.

It may also be possible to use a slightly simpler solution by converting from date to timestamp, which produces a result of the interval type, rather than a decimal.

The interval solution:

SELECT id,
       location,
          EXTRACT (DAY FROM date_diff)
       || ' days, '
       || EXTRACT (HOUR FROM date_diff)
       || ' hours, '
       || EXTRACT (MINUTE FROM date_diff)
       || ' minutes'
FROM   (SELECT   id, location, 
                 CAST(MAX (date) as timestamp) 
                 - CAST(MIN (date) as timestamp) AS date_diff
        FROM     your_table
        GROUP BY id, location)

Upvotes: 1

Emmanuel
Emmanuel

Reputation: 14209

This can be ok:

select T1.ID, T2.LOCATION, round(T2.DDATE - T1.DDATE) || ' days, ' ||
            trunc(mod((T2.DDATE - T1.DDATE)*24, 24)) || ' hours, ' ||
            trunc(mod((T2.DDATE - T1.DDATE)*24*60, 60)) || ' minutes' duration
from YOUR_TABLE T1, YOUR_TABLE T2
where T2.ID = T1.ID
  and T2.LOCATION = T1.LOCATION
  and T1.TYPE = 'Arrived'
  and T2.TYPE = 'Departed'
;

Upvotes: 0

Related Questions