PostgreSQL - How to convert seconds in a numeric field to HH:MM:SS

Question

I'm new to PostgreSQL (I have been using MS SQL for many years) and need to convert a numeric column which contains a time in seconds to HH:MM:SS format.

I have Googled and found that to_char(interval '1000s', 'HH24:MI:SS') works so I am attempting to use this with my field name:

to_char(fieldname, 'HH24:MI:SS') gives an error cannot use "S" and "PL"/"MI"/"SG"/"PR" together

to_char(fieldname::interval, 'HH24:MI:SS') gives an error cannot cast type numeric to interval

Can anyone show me where I am going wrong?

Answer 1

SELECT  TO_CHAR('1000 second'::interval, 'HH24:MI:SS') 

or, in your case

SELECT  TO_CHAR((mycolumn || ' second')::interval, 'HH24:MI:SS') FROM    mytable 

Answer 2

To convert seconds to HH:MM:SS

SELECT 120 * interval '1 sec'; 

You will get 00:02:00

To convert minutes to HH:MM:SS

SELECT 120 * interval '1 min';  

You will get 02:00:00