2 Replies Latest reply on Feb 26, 2013 2:05 AM by Richard Leeke

# Time in 5 minute interval

Hi all,

I have a datetime field such as with numerous rows like "01-Feb-2013 11:01:21 AM". How do I truncate these fields into 5 mins intervals?

For example,

"01-Feb-2013 11:01:21 AM" would be "01-Feb-2013 11:00:00AM"

"01-Feb-2013 11:07:21 AM" would be "01-Feb-2013 11:05:00AM"

"01-Feb-2013 11:09:41 AM" would be "01-Feb-2013 11:05:00AM"

Thanks,

Alan

• ###### 1. Re: Time in 5 minute interval

Hi Alan,

Creating a calculated field similar to the following should give you the desired result:

if int(mid(str([Datetime]), 14, 1))<5 then datetime(left(str([Datetime]), 13)+ "5:00" )

elseif int(mid(str([Datetime]), 14, 1))>5 then datetime(left(str([Datetime]), 12)+(str(int(mid(str([Datetime]), 13, 1))+ 1)+ "0:00" ))

end

Hope this helps!

-Tracy

• ###### 2. Re: Time in 5 minute interval

The way I prefer to do this is to convert the datetime to a floating point number, truncate it to the interval size I want and convert it back again. With this approach you can easily parameterise the interval size. I've written this up showing how to parameterise it for the soon to be launched (or relaunched) Tableau Calculation Reference Library - but here's a sneak preview:

DATETIME(INT(FLOAT([datetime]) * 288) / 288)

288 is the number of 5 minute intervals in a day (24 * 12).