So I have a total seconds difference from two timestamps, lets say:

Table Ex

Timestamp_A         | Timestamp_B         | Seconds
2022-10-12 15:19:02 | 2022-11-28 15:35:38 | 4,061,796
2022-11-21 09:58:25 | 2022-11-21 09:58:27 | 2

I used DATEDIFF('s', Timestamp_A, Timestamp_B) to produce the seconds.

I want to be able to convert the seconds to something like Days Hours:Minutes:Seconds or at least a way to represent days (DD:HH:MM:SS).

So for these two examples, I'd have: Table Ex

Timestamp_A         | Timestamp_B         | Seconds    | Converted
2022-10-12 15:19:02 | 2022-11-28 15:35:38 | 4,061,796  | 47 00:16:36
2022-11-21 09:58:25 | 2022-11-21 09:58:27 | 2          | 00 00:00:02

I tried messing around with to_varchar mixed around with to_timestamp but to no avail.

Any help is appreciated

CodePudding user response：

You can use this SQL UDF to do this. It's easier than having to complicate the SQL with the logic to calculate the formatted string:

create or replace function DHMS(sec int)
returns string
language sql strict immutable
as $$

    to_varchar(floor(sec/86400), '00') || ' ' 
    || to_varchar(dateadd(seconds, sec - floor(sec/86400) * 86400, '1970-01-01 00:00:00'), 'HH:MI:SS')

$$;


with T1 as
(
    select 
    COLUMN1::timestamp as Timestamp_A,
    COLUMN2::timestamp as Timestamp_B,
    COLUMN3::int as Seconds
    from (values
    ('2022-10-12 15:19:02', '2022-11-28 15:35:38', 4061796),
    ('2022-11-21 09:58:25','2022-11-21 09:58:27',  2))
)
select *, DHMS(seconds) from T1
;

Output:

If you need more room for the days, just add one or more additional zeros to the to_varchar(floor(sec/86400), '00') part in the format string for the days.