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.

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
0

2 Answers 2

Reset to default
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
2

Here:

> select (1449.448520410 units second)::interval hour(2) to second; (constant) 0:24:09 1 row(s) retrieved.
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.