Human readable elapsed time between many days

Question

I work with Oracle SQL and I have a table with 3 columns: Process, Start Date and End Date. I want to calculate the time duration for every process.

I used this query:

select 
(enddate.date_value - startdate.date_value) as duration
from dual

and the result is in days.

For example: The start date is 30.3.2016 17:14:53 and the end date is 8.7.2016 14:51:21

When I use the query, the result is 99.90032407407407407407407407407407407407, but I want result like this: 3 months, 7 days, 21 hours, 36 minutes, 28 seconds.

How can I do that?

Answer 1

Based on my previous answer you can create an Oracle function sinceHumanReadable:

exemple from https://momentjs.com/docs/#/plugins/preciserange/ produce the same result

moment("2014-01-01 12:00:00").preciseDiff("2015-03-04 16:05:06");
// 1 year 2 months 3 days 4 hours 5 minutes 6 seconds

http://sqlfiddle.com/#!4/d6783/1

create or replace FUNCTION sinceHumanReadable(start_date IN date,end_date IN date) 
    RETURN VARCHAR2
IS result VARCHAR2(255);

    BEGIN 

    with 
    dates_parts as (
    SELECT
        EXTRACT(YEAR   FROM (end_date - start_date) YEAR TO MONTH ) as Year,
        EXTRACT(MONTH  FROM (end_date - start_date) YEAR TO MONTH ) as Month,       
        EXTRACT(HOUR   FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Hour,
        EXTRACT(MINUTE FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Minute,    
        EXTRACT(SECOND FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Second
    FROM dual
    ),
    dates_parts_with_day as (
    select        
        Year,Month,Hour,Minute,Second,
        EXTRACT(DAY FROM (end_date - ADD_MONTHS(start_date,Month+Year*12)) DAY TO SECOND ) as Day
    from dates_parts
    )
    select
        decode(dates_parts_with_day.Year, 0,'', dates_parts_with_day.Year  || ' years ' )||
        decode(dates_parts_with_day.Month,0,'', dates_parts_with_day.Month || ' months ')||
        decode(dates_parts_with_day.Day,0,'', dates_parts_with_day.Day || ' days ')||
        decode(dates_parts_with_day.Hour,0,'', dates_parts_with_day.Hour || ' hours ')||
        decode(dates_parts_with_day.Minute,0,'', dates_parts_with_day.Minute || ' minutes ')||
        dates_parts_with_day.Second || ' seconds'
    into result
    from 
        dates_parts_with_day;

    RETURN(result); 

    END sinceHumanReadable;
GO

The query

with dates as (    
  select sysdate-99.90032407407407407407407407407407407407 as d1,sysdate  as d2 from dual
  union all
  select to_date('2016-03-30 17:14:53','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2016-07-08 14:51:21','yyyy-mm-dd hh24:mi:ss')   as d2 from dual
  union all
  select to_date('2014-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2015-03-04 16:05:06','yyyy-mm-dd hh24:mi:ss')   as d2 from dual  
  union all
  select sysdate as d1,add_months(sysdate,35)   as d2 from dual
  union all
  select sysdate as d1,sysdate as d2 from dual
)
select
    d1,d2,
    sinceHumanReadable(d1,d2) as since
from
    dates;

will produce :

|                   D1 |                   D2 |                                               SINCE |
|----------------------|----------------------|-----------------------------------------------------|
| 2017-07-19T17:50:00Z | 2017-10-27T15:26:28Z |      3 months 7 days 21 hours 36 minutes 28 seconds |
| 2016-03-30T17:14:53Z | 2016-07-08T14:51:21Z |      3 months 7 days 21 hours 36 minutes 28 seconds |
| 2014-01-01T12:00:00Z | 2015-03-04T16:05:06Z | 1 years 2 months 3 days 4 hours 5 minutes 6 seconds |
| 2017-10-27T15:26:28Z | 2020-09-27T15:26:28Z |                         2 years 11 months 0 seconds |
| 2017-10-27T15:26:28Z | 2017-10-27T15:26:28Z |                                           0 seconds |

Answer 2

The difference between two DATE values is a number representing the number of days. You seem to want an interval, this can be done using TIMESTAMP values.

select cast(enddate as timestamp) - cast(startdate as timestamp)
from the_table

The result of subtracting a timestamp from a timestamp is an interval.

Formatting of an interval value is however quite tricky in Oracle. See e.g. format interval with to_char