I have field with an integer value which means number of seconds. I would want to display it as MM:SS, without hours, so for example 6000 seconds should be shown as 100:00. What's the easiest way to do this? As far as I see, to_char cannot be used for this.
1 Answer
This is a kind of workaround, as I haven't found better solution:
You could use a simple select:
test=# select (6022/60)::TEXT || ':' || (6022%60)::TEXT; ?column? ---------- 100:22 (1 row) This could be wrapped in a nice function:
CREATE FUNCTION format(integer) RETURNS TEXT AS $$ select ($1/60)::TEXT || ':' || ($1%60)::TEXT; $$ LANGUAGE SQL STABLE; Example of usage:
test=# SELECT format(6); format -------- 0:6 (1 row) test=# SELECT format(60); format -------- 1:0 (1 row) test=# SELECT format(600); format -------- 10:0 (1 row) test=# SELECT format(6000); format -------- 100:0 (1 row) 