Manny Ezeagwula

Updated August 03, 2020 20:48

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

Answered

Follow

I have a timeseries field (example below) i'd like normalized to 10 or 15 minute intervals, how would I go about it?

 

Date
8/2/20 00:00
8/2/20 00:02
8/2/20 00:03
8/2/20 00:05
8/2/20 00:06
8/2/20 00:07
8/2/20 00:15
8/2/20 00:17
8/2/20 00:18
8/2/20 00:20

 

• Official comment

  

  Manny Ezeagwula August 03, 2020 20:57

  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.