Rounding time

[#1]

I need some help on rounding. I have some values that I want to round either to the closest hour or closest half hour.

for example: (This is a 24 hour clock, not 12)

00.28.26 should be 00.30.00

07.58.26 should be 08.00.00

19.58.26 should be 20.00.00

20.29.00 should be 20.30.00

Any idea how to go about this?

My field with the time data is called "Metrics Time"

[swuehl]

I assumed you have set your time format accordingly and your field is interpreted as time value.

Try

LOAD

[Metrics Time],

time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime,

...

FROM ...;

[#3]

Swuehl to the rescue! As always!

That function loads but the new field doesn't have any data.

Perhaps I haven't done the correct formatting?

[swuehl]

If you haven't set the correct standard time format, your field is probably not correctly interpreted, try

Try

LOAD *,

time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime;

LOAD

time#([Metrics Time],'hh.mm.ss') as [Metrics Time]

...

FROM ...;

If you are coping with intervals rather than time values (i.e. values can exceed 24h), use the interval#() / interval() functions.

Hope this helps,

Stefan

[#5]

I guess im kind of confused as to where im putting all this...

You'd think I would have gotten it by now.

[swuehl · Accepted solution]

If above is your general format you want to use as time format, use

SET TimeFormat='hh.mm.ss';

at the top of your script. If so, I would recommend to use the standard format.

If you interpret a time format using time#() function, this will only affect the field you are applying this function to ([Metrics Time2] in your example). So if you use TIME again in the calculation for the rounded value, the format is not interpreted with the 'special' format code. You can do a preceding load like I've shown above or just embed the format interpretation into the round:

time(round( time#([Time],'hh.mm.ss'),maketime(0,30))) as RoundedMetricsTime;

[#7]

Im pretty sure you know everything about this program!

Thanks again.

[#8]

Hey guys,

to round the time is also very important to show the correct interval value in the format hh:mm:ss. I've had the problem, that QV shows the floor-value of an interval.

e.g.:

0,00024722223 = 00:05:00

0,00024722221 should be rounded to 00:05:00, but QV shows 00:04:59

By using the expression round(value,maketime(0,0,1)), QV rounds the value to the nearest full second. In my example it is 00:05:00.

Bye,

Riffi