Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I want to convert decimal numbers, in the form of 70.345678901234 (seconds), into mm:ss. I found some solutions in the forums and tried quite a few but still can't figure out what I'm doing wrong. I also tried changing the decimal separator and shortening the numbers to less than 14-digits.
Can you help with that?
Thanks,
Ivelin
Use the time() function and round() also to remove some digits
1 day = 1, 12 hrs= 0,5
so if your field is expressed in seconds, you will need to divide by 3600*24
sth like time(YourField/3600/24, 'hh:mm:ss') as YourFiled
Fabrice
Hi,
You can use timestamp()
like this,
=Timestamp(date,'mm:ss')
Hi, try this:
In your script, in the load statement, write instead of your field:
timestamp(timestamp#(round(Your Field),'mm:ss'),'mm:ss') as New Field
Greetings,
Lucas
EDIT: Fabrice made a very good point, changes are in bold
Use the time() function and round() also to remove some digits
1 day = 1, 12 hrs= 0,5
so if your field is expressed in seconds, you will need to divide by 3600*24
sth like time(YourField/3600/24, 'hh:mm:ss') as YourFiled
Fabrice
That would work.
T1:
LOAD * INLINE [
date, responseTime
20140101, 12.72959423502355
20140110, 13.28933262233888
20140120, 99.99893392359389
20140131, 11.18933455334568
];
TEST:
LOAD
date,
'00:00:'&responseTime as Test
Resident T1;
And then format your new field to be represented as Time
Hope this helps
Antoine
Hi Fabrice,
your solution works best if I keep Replace(responseTime,'.',',') in LOAD.
However, there is a problem with the rounding. It doesn't take into account the first decimal digit and 99.99.. is 1:39 instead of 1:40. Any ideas about that?
Ivelin
I did not recommend to use replace()
Did you try to use the round() function : time(round(seconds, 1)/24/3600, 'hh:mm:ss')
You are right, the time() function does not round the seconds, it takes the integer part (like floor() function)
Fabrice
Now it works perfect!
As for replace(), If I don't use it then time() in expressions returns null. That's also the case with Lucas' solution. I guess it's a conflict with the decimal separators..
Ivelin