7 Replies Latest reply: Jul 4, 2012 4:19 PM by Juan Lopez

Converting a millisecond value to a Time value

Hi,

I'm hoping this has an easy answer I'm not seeing but here goes.

I'm trying to convert a millisecond value field from a table to show as a time value, for example 7,200,000 milliseconds to show as 02:00:00 and 8,100,000 to show as 02:15:00.

I'm looking through all of the functions and can't seem to find an easy way to do this with hundreds of records in this table, all having this issue. I know I need to put something in the load statement but I don't know what.

Can anyone tell me if there is an easy way to do this?

Thanks,

Chris

• Converting a millisecond value to a Time value

something like

=

=

time(100+(2700000/(1000*60*60*60*9)),'hh:mm:ss')

replace 2700000 with your values name..

• Converting a millisecond value to a Time value

If this is a duration instead of a time of day, you'd want to use the interval() function instead of the time() function. Time() is a time of day, so is limited to 24 hours. Even if you're never more than 24 hours, I think it's more correct to use interval(), even if they'd be functionally equivalent in that case. Also, I'm thinking you want to round to the nearest second.

So modifying Jesper's solution a little:

interval(round(YourMillisecondsField/1000)/(60*60*24),'hh:mm:ss') as YourNewField

• Converting a millisecond value to a Time value

Agree, it is logically correct and relaible for any number

• Converting a millisecond value to a Time value

Hi guys!

I'm new on forum and I would like a bit help about milliseconds.

How may I convert it to Date?

I used
Date(CREATEDTIME/1000/60/60/24, 'DD/MM/YYYY')
The result is a date, but from the year 1940.

I'm looking for Gregorian Calendar, am I on the rigth way or do you know a better solution?

Thanks

• Re: Converting a millisecond value to a Time value

just add the offset (in days) at which your database time reference is set....

that would make :

time from DB => day 1 month 2 year 1 to be :

time +offset => 1/02/2012

given offset is makedate(2012)

• Converting a millisecond value to a Time value

This will work:
=time(MillisecondsField/(1000*60*60*24),'hh:mm:ss')

• Converting a millisecond value to a Time value

Hi,

Thanks all for the suggestions! Using Interval does indeed give me what I need, this will no doubt make my reports a bit more readable.

Thanks again

Chris