parsing millisecond time stamps

[#1]

Hello (again)!

I'm having continued struggles with millisecond-level times. I'm loading an external text file that has timestamps like this:

2014-01-01 23:59:41.600

2014-01-01 23:59:41.950

In the script, I read the field like this:

FRAC(TIMESTAMP#(external_timestamp_field,'YYYY-MM-DD hh:mm:ss.fff'))     to get just the timestamp

I also want to round that DOWN to the nearest whole second. In other words, I want to truncate away the milliseconds. I'm trying to do it like this:

FLOOR (FRAC(TIMESTAMP#(external_timestamp_field,'YYYY-MM-DD hh:mm:ss.fff')), 1/(24*60*60)).

I *think* this leaves me in a good place. Note that I'm not forcing any formatting codes, so these values appear in QV as long, ugly decimals.

Much later, I'm aggregating on the truncated-to-nearest-second field, but the aggregations are misbehaving. Am I treating these times properly?

[Anonymous]

Hi

Try this :

Frac ( round ( TIMESTAMP#(external_timestamp_field,'YYYY-MM-DD hh:mm:ss.fff') , 1/(24*60*60 ) ) )

I often test things like this by putting a test expression like this in a text and observing the result :

=time(Frac ( round ( now() , 1/(24*60*60 ) ) ) , 'YYYY-MM-DD hh:mm:ss.fff' )

& chr(10) &

Frac ( round ( now() , 1/(24*60*60 ) ) )

Warning:     Do not leave an object with now() in an expression in it after you have finished testing otherwise it it will just tick over unattended continuously chewing up cpu.

Best Regards,     Bill

[hic]

I think both your formulae look good. But I am not sure I like the thought of aggregating a number that already has had the integer part cut off, and then is rounded...

If you sum a large number of timestamps, you will accumulate all rounding errors. And if they sum up to more than 24 hours, what timestamp will you get then?

If you average the timestamps, you will get strange result in situations where one timestamp is just before midnight and one is just after midnight. They will average to lunchtime...

A comment: Forcing format codes or not should not make any difference on the performance or the evaluation. I would use formatting, since I then easier will spot errors.

HIC

[#4]

Uh oh... any time a question makes it onto your radar, it must be bad!

Regarding averaging times, that was mostly driven by desperation. I'm still struggling to parse millisecond-level times. I had tried to round down to the nearest second (destroying sub-second granularity). I was averaging data points within each second as a way to account for the multiple, millisecond-level data points. This was a poor choice to begin with.

My question above is still open... I eventually gave up with treating the times as numbers. I force them to all to be text. I work with forced-text time values down to the millisecond. At the very end, I'm going to try to parse them back into "real" times. I know this is bad, but it's the only way I can get things to work.

[#5]

So.. I gave up on making the times (with full millisecond granularity) work as times. I'm forcing them all to text using TEXT(). Everything else I tried would end up with failed joins on time values that looked like they were identical. Once I forced to text, the joins worked.

[#6]

Do you still need the milli seconds in the timestamp then SET the Time stamp format to below, it keeps the milli seconds by default.

SET TimestampFormat='D/M/YYYY hh:mm:ss fff';

Even though if you can't use the below format still the field have milliseconds and you have to use the tiimestamp function to show the Milli seconds.

If you want to round the nearest second, use the Floor function like below:

Floor(TimestampField , 1/(24*60*60))  This statements round the milli second to nearest second

[#7]

Please change the TimeFormat variable to SET TimestampFormat='D/M/YYYY hh:mm:ss.fff'; and try

[#8]

I tried changing the master time stamp format. As I understand this, it's not a format problem, it's an underlying data problem. If I force my time stamps to look like numbers (with full 14 decimal precision), I see different values. No amount of floor/round/trunc made those go away.

[#9 · Accepted solution]

So... I had the luxury of solving this problem by running away from it. I was able to get back to the source of the data and change how it was delivering my timestamps. Essentially, my source file now has a MillisecondOfDay field than ranges from 0 (00:00:00.000) to 86399999 (23:59:59.999).

Had I not been able to solve that problem upstream from QV, I planned to build up to the MillisecondOfDay by isolating the hour, minute, second from the timestamp and multiplying each by the appropriate multiple.

Finally, I work with my MillisecondOfDay throughout, and only turn that back into a time (by dividing it by 86400000) at the last moment necessary. Whenever I do that, I add in a Floor(n,1/86400000) just to be safe.

[swuehl]

Is there a special reason to keep your data at the millisecond granularity? Do you really need this fine granularity?

Having fact data with field values with a finer granularity than needed for your analysis will increase your data size, here is some more information about that:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/11/the-importance-of-being-distinct