Man-readable elapsed time between many days

Question

I am working with Oracle SQL and I have a table with three columns: Process , Start Date and End Date . I want to calculate the length of time for each process.

I used this query:

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

and the result is in a few 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 to get the result as follows: 3 months, 7 days, 21 hours, 36 minutes, 28 seconds .

How can i do this?

Answer 1

This complex request (specifically for DAY !!):

To calculate the correct Day , 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 create another date:

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

Answer 2

The difference between the two DATE values ​​is number , representing the number of days. It seems you need interval , this can be done using TIMESTAMP values.

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

The result of subtracting TIMESTAMP from TIMESTAMP is the interval.

Formatting the interval value, however, is quite complicated in Oracle. See format interval with to_char

Answer 3

According to my previous answer, you can create an Oracle function sinceHumanReadable :

An example from https://momentjs.com/docs/#/plugins/preciserange/ gives 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 

Request

 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 |