Additional Date Aggregations: Aggregating by 5 Minute Intervals

I worked with a customer recently who wanted to aggregate their data in 5 minute intervals. We had a clean way to implement this:

from_unixtime(300*floor(unixtime/300))

How this works:

We divide the unixtime by 300 (number of seconds in 5 minutes), and take the floor of that result to get 5 minute buckets. To display the result as a nicely formatted timestamp, we then multiply this result back by 300 and use a from_unixtime function. In Redshift, this would be a to_timestamp function.

If your timestamp isn't already in unixtime, you can leverage some handy functions like this one!

You can generalize this expression and make your own custom aggregation filter (details on setting us a customer aggregation filter can be found in this community post!) Periscope Data also has other shortcuts for commonly used date aggregations, listed here.

Any other aggregation types you would like to see? Comment below!

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • This is how we handle it:

    when '[Period]'    = '5m' then dateadd('minutes', (extract(minute from [date:pst]) / 5) *5 , [date:pst:hour] )

    Reply Like 2
    • David Krysl This was very helpful, worked for me in Redshift, thank you!

      Reply Like 1
Like Follow
  • 3 mths agoLast active
  • 2Replies
  • 652Views
  • 3 Following