3

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?

Improve this question
3
  • 1
    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
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.