Question 114821 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to format a big sum of seconds as hrs and mins?

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
Tags:

2

Answers


  1. 0 
    select  ((54000000300/3600.0)::text || 'hrs')::interval;
    interval    
----------------
 15000000:05:00
    Login or Signup to reply.
  2. 0

    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:

    And the cast to bigint is only needed if it’s 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search