Converting a millisecond value to a Time value

[chris_johnson]

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

[#2]

something like

=

=

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

replace 2700000 with your values name..

[Anonymous]

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

[johnw · Accepted solution]

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

[Anonymous]

Agree, it is logically correct and relaible for any number

[chris_johnson]

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

[#7]

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

[#8]

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)