Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7652

Add time_bucket() Function for Time Series Analysis

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.17.0
    • 1.18.0
    • None

    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. 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;
      ```

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            cgivre Charles Givre
            cgivre Charles Givre
            Igor Guzenko Igor Guzenko
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment