# Convert seconds into HH:MM:SS in Informix

How is it possible to convert a float number of seconds to HH:MM:SS in Informix. I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. There are many ways of going about this, but I am struggling to find a method of doing so.

I have identified that running the below gives close to what I want, but excludes the hours:

``````select b.run_duration, floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time from ph_task a, ph_run b where a.tk_id = b.run_task_id order by run_duration DESC
``````

output:

``````24:09
``````

What I would like to see is:

``````00:24:09
``````

How can I customize my SQL to provide that?

• I wonder if you can use the `interval` datatype to accomplish this? I lack a fiddle environment to try myself. – Colin 't Hart Dec 28 '20 at 11:36
• You should look at this question on Stack Overflow: I want to convert an integer into hh:mm:ss format in Informix database. The integer there is in milliseconds, so the first step is to convert to a floating-point number. One downside of using the INTERVAL data type is that its fractional precision is limited to 5 decimal places. You're showing 9 decimal places. You could still convert the integer portion using INTERVAL manipulation, and then add the fraction to the string result. – Jonathan Leffler Dec 28 '20 at 14:51
• If that's seconds - I'd question the value of having 9 decimal places - You might want to start by rounding to 6 places? – Vérace Dec 29 '20 at 8:15

## 1 Answer

Using the below sql, I managed to give exactly what I am looking for. I used the number 6346 to display the output populating hours, minutes and seconds.

Query used:

``````select lpad(floor(6346 / 60 / 60),2,'0') || ':' || lpad(mod(6346/60, 60), 2, '0') || ':' || lpad(mod(6346,60), 2, '0') as run_duration_time from systables
``````

Output:

``````01:45:46
``````

Another example without hours:

``````select lpad(floor(1000 / 60 / 60),2,'0') || ':' || lpad(mod(1000/60, 60), 2, '0') || ':' || lpad(mod(1000, 60), 2, '0') as run_duration_time
``````

from systables

Output:

``````00:16:40
``````

Another example without hours and minutes:

``````select lpad(floor(45 / 60 / 60),2,'0') || ':' || lpad(mod(45/60, 60), 2, '0') || ':' || lpad(mod(45, 60), 2, '0') as run_duration_time
``````

from systables

Output:

``````00:00:45
``````