We 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. Sisense for Cloud Data Teams also has other shortcuts for commonly used date aggregations, listed
here.
Any other aggregation types you would like to see? Comment below!
