get minutes and seconds from query in postgresql

Question

I need a time in minutes and seconds. If minutes is greater than 60 I then add to minutes, but do not convert hours

Query :

TO_CHAR((sum(seconds)|| ' second')::interval,'HH24:MI:SS') 
as duration from table;

Output:

DURATION
02:50:21

Required Output:

DURATION
170:21

even i have tried another query (without HH24) but i get below out put:

Query :

TO_CHAR((sum(seconds)|| ' second')::interval,'MI:SS') as duration from table;

Output:

DURATION
50:21

Here we can see from 1st query output 02 hrs means 120 minutes + 50minutes =170 minutes and seconds same as it is.

is it possible directly getting from query or not?

just somebody · Accepted Answer · 2014-04-25 13:28:12Z

3

you don't need to_char here:

select (sum(seconds) / 60)::text || ':' || (sum(seconds) % 60) ...;

answered Apr 25, 2014 at 13:28

just somebody

19.4k6 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

You may still want to_char for the seconds part, as you want the leading 0 if the seconds value is 0-9.