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

    Time in 5 minute interval

    Alan Kwan

      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"




        • 1. Re: Time in 5 minute interval
          Tracy Rodgers

          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" ))



          Hope this helps!



          • 2. Re: Time in 5 minute interval
            Richard Leeke

            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).