SQL Athena/Presto to convert time duration to DD HH MM format

Question

I have tables with 2 columns of timestamp. My goal is to have the difference between those 2 timestamp columns, but the format should be DD HH MM.

The table looks like below:

Using date_diff function, I can get the time difference in minutes, but I'm stuck at what to do next. What I would like to have is table like below:

ID	Time 1	Time 2	datediff(minutes)	time difference
A	2023-06-01 07:30:12	2023-06-12 10:31:54	16021	11d 3h 1m
B	2023-06-01 09:30:45	2023-06-01 10:31:50	61	0d 1h 1m

Any idea how to do it in Athena/Presto? Thank you for the help

Answer 1

You can subtract two dates which will result in interval day to second and then format it "manually":

-- sample data
WITH dataset (id, Time1, Time2) AS (
    values
        ('A',   timestamp '2023-06-01 07:30:12', timestamp '2023-06-12 10:31:54'),
        ('B',   timestamp '2023-06-01 09:30:45', timestamp '2023-06-01 10:31:50')
)

-- query
select *,
    date_diff('minute', time1, time2) "datediff(minutes)",
    cast(day(Time2 - Time1) as varchar) || 'd ' || cast(hour(Time2 - Time1) as varchar) || 'd ' || cast(minute(Time2 - Time1) as varchar) || 'm'
from dataset;

Output:

id	Time1	Time2	datediff(minutes)	_col4
A	2023-06-01 07:30:12	2023-06-12 10:31:54	16021	11d 3d 1m
B	2023-06-01 09:30:45	2023-06-01 10:31:50	61	0d 1d 1m