This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
on 03-02-202308:57 AM - edited on 01-30-202401:46 PM by DRay
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!
