I'm using this query to get the difference between two dates in oracle the result should display num of days, hours, minutes I did it like that

select r.created_date, r.updated_date, (r.updated_date - r.created_date) time from orders r;

result like that:-

[created_date] 01/01/20 07:00:00.000000000 PM    
-------                    
[updated_date] 21/01/20 06:20:00.000000000 AM   
------
[time]  19 11:20:00.000000

how to format 19 11:20:00.000000 to represent num of days, hours, and min in a readable way? ex: 19 days, 11 h, 20 min how to set aliases name for time column?

thanks.

CodePudding user response：

The result of subtracting two timestamps is an interval data type, which you can't format directly.

You could extract each component individually, which to avoid repetition could be done in a CTE, e.g.:

with cte as (
  select r.created_date, r.updated_date, (r.updated_date - r.created_date) time
  from orders r
)
select cte.created_date,
  cte.updated_date,
  extract(day from cte.time) || ' days '
    || extract(hour from cte.time) || ' hours '
    || extract(minute from cte.time) || ' minutes'
    as time
from cte;

db<>fiddle

You've shown a simple examples, but you may want to include fractional minutes, or show the seconds or even milliseconds as separate parts of the text. I've included a couple of variations in the fiddle.

As @WernfriedDomscheit mentioned in a comment, if the interval is negative - which is unlikely when comparing created/updated like this, but still useful to know - all the extracted components are negative, as shown in this modified db<>fiddle.

You could also use case expressions to skip components that are zero, which could get messy.

CodePudding user response：

If your columns have TIMESTAMP data types then:

select created_date,
       updated_date,
       EXTRACT(DAY FROM updated_date - created_date) || ' days '
       || EXTRACT(HOUR FROM updated_date - created_date) || ' hours '
       || EXTRACT(MINUTE FROM updated_date - created_date) || ' minutes '
       || EXTRACT(SECOND FROM updated_date - created_date) || ' seconds'
         AS time
from   orders;

If your columns have DATE data types then use a similar method but you have to explicitly cast the difference to an INTERVAL DAY TO SECOND:

select created_date,
       updated_date,
       EXTRACT(DAY FROM (updated_date - created_date) DAY TO SECOND) || ' days '
       || EXTRACT(HOUR FROM (updated_date - created_date) DAY TO SECOND) || ' hours '
       || EXTRACT(MINUTE FROM (updated_date - created_date) DAY TO SECOND) || ' minutes '
       || EXTRACT(SECOND FROM (updated_date - created_date) DAY TO SECOND) || ' seconds'
         AS time
from   orders;

Which, for the sample data:

CREATE TABLE orders (
  created_date TIMESTAMP,
  updated_date TIMESTAMP
);

INSERT INTO orders VALUES (TRUNC(SYSTIMESTAMP-1), SYSTIMESTAMP);

Outputs:

CREATED_DATE	UPDATED_DATE	TIME
2021-10-30 00:00:00.000000	2021-10-31 16:37:08.952012	1 days 16 hours 37 minutes 8.952012 seconds

db<>fiddle here