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?

As soon as you want units larger than days, you've got problems. You have to pick whether you're going to lie and have standard length months (e.g. all 30 days) or you have a situation where "3 months, 7 days, ..." can represent different actual periods of time, depending on exactly *when* in the year the period occurred. Neither is particularly appealing. Are you sure you actually need what you're asking for here, and that those asking for it understand these kind of trade-offs? — Damien_The_Unbeliever, Oct 27 '17 at 13:29
@Damien_The_Unbeliever, I agree with you, if you want convert just a number of day, but if you want a duration repesentation between 2 dates you don't need a fixed number of day per month (e.g all 30 days) or a fixed number of day per year, look my proposal (I'm sure we can simplify/optimise it, but it seems work !) — Indent, Oct 27 '17 at 14:24
@Indent - what I was trying to indicate is that "3 months, 7 days, ..." may represent different periods of time if you don't adopt fixed length months. I.e. it's the period between 1st Feb and 8th May 2000 and represents an actual period of 97 days. A year later between those same dates, it represents 96 days. And in the OPs example, it represents 99 days. — Damien_The_Unbeliever, Oct 27 '17 at 14:31
like this https://momentjs.com/docs/#/plugins/preciserange/ Read my answer I have never adopt a fixed number of month in my query and I find the expected result. In the OPs example, it represent the duration between 30.3.2016 17:14:53 and 8.7.2016 14:51:21 — Indent, Oct 27 '17 at 14:49
I have added test with `moment.js` exemple, my code produce the same result. — Indent, Oct 27 '17 at 14:56

Indent · Answer 1 · 2017-10-27T15:03:50.703

This complex query (specialy for DAY !!) :

To compute a correct Day I add Month and 12 * Year to the original date.

with dates as(
select
   sysdate       as d1,
   sysdate-99.90032407407407407407407407407407407407-365  as d2
from
   dual
),
dates_parts as (
SELECT
    d1,
    d2,
    EXTRACT(YEAR   FROM (d1 - d2) YEAR TO MONTH ) as Year,
    EXTRACT(MONTH  FROM (d1 - d2) YEAR TO MONTH ) as Month,
    EXTRACT(DAY    FROM (d1 - d2) DAY TO SECOND ) as Day,
    EXTRACT(HOUR   FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Hour,
    EXTRACT(MINUTE FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Minute,    
    EXTRACT(SECOND FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Second
FROM dates
)
select
    dates_parts.Year,
    dates_parts.Month,
    dates_parts.Day,
    dates_parts.Hour,
    dates_parts.Minute,
    dates_parts.Second,
    EXTRACT(DAY FROM (d1 - ADD_MONTHS(d2,Month+Year*12)) DAY TO SECOND ) as Day_Corrected
from 
    dates_parts

will produce the different date part :

| YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | DAY_CORRECTED |
|------|-------|-----|------|--------|--------|---------------|
|    1 |     3 | 464 |   21 |     36 |     28 |             7 |

score 1 · Answer 2 · answered Oct 27 '17 at 08:43

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

Actually it is sufficient to cast only one, either `enddate` or `startdate`. — Wernfried Domscheit, Oct 27 '17 at 09:48

Indent · Answer 3 · 2017-10-27T15:29:26.323

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 |

Human readable elapsed time between many days

3 Answers3