topic Converting a millisecond value to a Time value in QlikView

Converting a millisecond value to a Time value

chris_johnson — Fri, 05 Nov 2010 16:42:35 GMT

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

Fri, 05 Nov 2010 16:57:24 GMT

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

Anonymous — Fri, 05 Nov 2010 19:48:41 GMT

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

Converting a millisecond value to a Time value

johnw — Fri, 05 Nov 2010 19:54:11 GMT

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

Anonymous — Fri, 05 Nov 2010 19:59:10 GMT

Agree, it is logically correct and relaible for any number

Converting a millisecond value to a Time value

chris_johnson — Mon, 08 Nov 2010 09:37:57 GMT

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

Converting a millisecond value to a Time value

Fri, 03 Dec 2010 14:57:05 GMT

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

Wed, 04 Jul 2012 20:19:25 GMT

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)