[DRILL-7652] Add time_bucket() Function for Time Series Analysis

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.18.0
• Labels: doc-impacting ready-to-commit
• Assignee: Charles Givre
• Reporter: Charles Givre
• Reviewer: Igor Guzenko
• Created: 20/Mar/20 13:13
• Updated: 21/Mar/20 10:27
• Resolved: 21/Mar/20 10:27

Description

These functions are useful for doing time series analysis by grouping the data into arbitrary intervals. See: https://blog.timescale.com/blog/simplified-time-series-analytics
-using-the-time_bucket-function/ for more examples.

There are two versions of the function:

• `time_bucket(<timestamp>, <interval>)`
• `time_bucket_ns(<timestamp>,<interval>)`

Both functions accept a `BIGINT` timestamp and an interval in milliseconds as arguments. The `time_bucket_ns()` function accepts timestamps in nanoseconds and `time_bucket
()` accepts timestamps in milliseconds. Both return timestamps in the original format.

1. 1. 1. Example:
         The query below calculates the average for the `cpu` metric for every five minute interval.

```sql
SELECT time_bucket(time_stamp, 30000) AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 12;
```

Issue Links

• links to: GitHub Pull Request #2033