I have a string column durationinseconds. I want to take sum(durationinseconds) and make it human-readable as hrs and mins.

Example:
Total sum of durationinseconds is 54000000300 seconds.
Now I want to convert and get it as:

15000000 hrs 5 min

Want to query this into Apache superset.

I tried to use query below but not getting correct value:

SELECT 
    sum(durationInSeconds)
    , FLOOR( ( sum(durationInSeconds) / 3600 ) - FLOOR( sum(durationInSeconds )/ 86400 ) * 24 ) AS HOURS 
    , FLOOR( ( sum(durationInSeconds )/ 60 ) - FLOOR( sum(durationInSeconds) / 3600 ) * 60 ) AS MINUTES 
 FROM tickets 

Below is the output I am getting which is not correct:

duration in seconds=395069295299 hr=6 min =54

CodePudding user response：

Cast to integer (bigint to hold the big value) and multiply with interval '1 sec':

SELECT '54000000300'::bigint * interval '1 sec';

Use to_char() to format exactly as requested:

SELECT to_char('54000000300'::bigint * interval '1 sec', 'FMHH24" hrs "FMMI" min"');

db<>fiddle here

Applied to your query:

SELECT to_char(sum("durationInSeconds"::bigint) * interval '1 sec', 'FMHH24" hrs "FMMI" min"')
FROM   tickets 

Assuming you actually double-quoted the unfortunate CaMeL-case name "durationInSeconds". Else drop the double-quotes. See:

• Are PostgreSQL column names case-sensitive?

And the cast to bigint is only needed if its really a "string column" - also an unfortunate design for a numeric or interval quantity.

The manual:

to_char(interval) formats HH and HH12 as shown on a 12-hour clock, for example zero hours and 36 hours both output as 12, while HH24 outputs the full hour value, which can exceed 23 in an interval value.

FM in the template pattern stands for:

fill mode (suppress leading zeroes and padding blanks)

The multiplication is typically faster than string concatenation.
to_char() is also fast and deals with corner cases nicely.

CodePudding user response：

select  ((54000000300/3600.0)::text || 'hrs')::interval;
    interval    
----------------
 15000000:05:00