What can we help you with?

e.g. Getting Started, Build Dashboards

How do i round-up/down a date field to specified intervals?


1 comment

  • Official comment
    Manny Ezeagwula

    One option is using the Cloud9QL function (FLOOR) to normalize the date/time field. This works by converting the timestamp field to EPOCH milliseconds in the desired format. 

    For example, to convert to 15-min intervals, the syntax would be as follows: floor(Date/900000)*900000 

    Where 900000 or (60*15*1000) is the number of milliseconds for the desired interval. The breakdown comes out to be:

    • 60 - number of seconds in a minute
    • 15 - number of minutes for the desired interval
    • 1000 - number of milliseconds in a second

    To change into 10-minute intervals, the function becomes floor(Date/600000)*900000 or (60*10*1000)

    Comment actions Permalink

Please sign in to leave a comment.