snippetMinor
Convert seconds into HH:MM:SS in Informix
Viewed 0 times
convertintosecondsinformix
Problem
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:
output:
What I would like to see is:
How can I customize my SQL to provide that?
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 DESCoutput:
24:09What I would like to see is:
00:24:09How can I customize my SQL to provide that?
Solution
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:
Output:
Another example without hours:
from systables
Output:
Another example without hours and minutes:
from systables
Output:
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 systablesOutput:
01:45:46Another 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_timefrom systables
Output:
00:16:40Another 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_timefrom systables
Output:
00:00:45Code Snippets
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 systablesselect lpad(floor(1000 / 60 / 60),2,'0') || ':' || lpad(mod(1000/60, 60), 2, '0') || ':' || lpad(mod(1000, 60), 2, '0') as run_duration_timeselect lpad(floor(45 / 60 / 60),2,'0') || ':' || lpad(mod(45/60, 60), 2, '0') || ':' || lpad(mod(45, 60), 2, '0') as run_duration_timeContext
StackExchange Database Administrators Q#282234, answer score: 2
Revisions (0)
No revisions yet.