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?

fieldname is an integer ? further, what output do you expect if you have an interval bigger than one day ? — leonbloy
– leonbloy, Commented May 25, 2010 at 14:49
@Quassnoi: Correct. Just wondered if that's what the poster wanted. — leonbloy
– leonbloy, Commented May 25, 2010 at 15:42
@leonbloy: yes, it is a numeric(18,0). It is possible for it to be > 24 hours but highly unlikely - it's JIRA work logs I'm looking at — Shevek
– Shevek, Commented May 27, 2010 at 8:51

Quassnoi · Accepted Answer · 2010-05-25 14:45:39Z

69

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

or, in your case

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

answered May 25, 2010 at 14:45

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

7 Comments

araqnid Over a year ago

or mycolumn * '1 second'::interval which will avoid reparsing the textual format over and over (although it doesn't make much practical difference in this case)

Ankit Doshi Over a year ago

@Quassnoi is it possible if i will get 1:20:30s then convert into 80:30 please help me

Quassnoi Over a year ago

@AnkitDoshi: please post it as another question.

bcfurtado Over a year ago

The OP didn't ask but if you came here trying to make the same transformation using Redshift, this will work because isn't actually supported.

Tom Bush Over a year ago

For some reason this didn't work for me until I explicitly used a concat() on mycolumn and second ... but then perfect so thank you.

|

Ingo Karkat · Accepted Answer · 2016-09-23 06:49:28Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related