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?

3
  • 1
    I wonder if you can use the interval datatype to accomplish this? I lack a fiddle environment to try myself. Commented Dec 28, 2020 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. Commented Dec 28, 2020 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
    Commented Dec 29, 2020 at 8:15

2 Answers 2

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
1

Here:

> select (1449.448520410 units second)::interval hour(2) to second;


(constant) 

  0:24:09

1 row(s) retrieved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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