Previous Next JavaScript must be enabled to correctly display this content

## timestamp_bucket function

The `timestamp_bucket` function rounds the given timestamp value to the beginning of the specified interval (bucket). The interval starts at a specified origin on the timeline.

You can use this function for aggregating time series data to a desired time interval, known as periodicity. In certain cases, it is desirable to place all your time series data into equidistant buckets of given periodicity, with each bucket representing the same amount of time.

Syntax:

    TIMESTAMP timestamp_bucket(<timestamp>[, interval [,origin ])

Semantics:

- timestamp: The `timestamp` argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type.
- interval: The `interval` argument is optional and a STRING data type. The `interval` is specified as `<n> unit`.

  where,

  `n` specifies the value of the interval. The `n` must be > 0

  `unit` defines the interval component. The function supports WEEK, DAY, HOUR, MINUTE, and SECOND in either singular or plural format.

  For example, "5 MINUTE" or "5 MINUTES".

  Note:

  The units are not case-sensitive.
- origin: The `origin` argument represents the starting point of buckets on the timeline. This argument is optional and takes a TIMESTAMP value. The origin can be of any data type that can be cast to TIMESTAMP type. If not specified, Unix epoch 1970-01-01 is the default value.

  Note:

  The function also rounds the input timestamps that are lesser than the `origin` to the beginning of the specified interval. That is, you can supply an `origin` with a future timestamp value as compared to the input timestamp value on the timeline.
- Return Value: TIMESTAMP(9)
  The function returns NULL in the following cases:
  - If any of the arguments are set to NULL.
  - If the input `timestamp` is not castable to TIMESTAMP type.

Example 12-20 Fetch the statistics of the number of passengers departing from the IST airport every 12 hrs with buckets starting from January 1st, 2019. Consider data only for the month of February 2019

    SELECT $t AS DATE,
    count($t) AS FLIGHTCOUNT
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f,
    timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
    WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
    GROUP BY $t
    ORDER BY $t

Explanation: To consider passengers traveling in February 2019, use the timestamp_floor function and round down the `flightDate` to the beginning of the month. Compare the result with the string "2019-02-01T00:00:00Z". This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To include the transit flights from the IST airport, use the array constructor [ ] to indicate that the `flightLegs` is an array and consider each `fltRouteSrc` array element in the search.

Use the `timsestamp_bucket` function on the `flightDate` fields with interval as 12 hours and origin as 1st of January 2019.

Output:

    {"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
    {"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
    {"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}

Example 12-21 From the Streaming media service data, fetch the statistics of number of accounts that expire each week with buckets starting from noon of January 1st, 2023. Consider data from 1st of December 2023

    SELECT array_collect(s.account_expiry) as ACCOUNT_EXPIRY,
    $t AS WEEK,
    count($t) AS ACCOUNTS
    FROM stream_acct s,
    timestamp_bucket(s.account_expiry, '1 week', '2023-01-01T12') $t
    WHERE s.account_expiry >= '2023-12-01'
    GROUP BY $t
    ORDER BY $t;

Explanation: Use the `timsestamp_bucket` function on the `account_expiry` fields with `interval` as 1 week and origin as 1st of January 12 PM. The function calculates a periodicity of a week from 1st of January 2023, 12 PM and rounds the `account_expiry` values to the beginning of the corresponding week bucket.

Notice that you use the array_collect function to display the `account_expiry` fields. You can reference only grouping expressions, aggregate functions, or external variables when the SELECT expression includes a grouping function.

Output:

    {"ACCOUNT_EXPIRY":["2023-12-18T00:00:00.000000000Z"],"WEEK":"2023-12-17T12:00:00.000000000Z","ACCOUNTS":1}
    {"ACCOUNT_EXPIRY":["2023-12-31T00:00:00.000000000Z"],"WEEK":"2023-12-24T12:00:00.000000000Z","ACCOUNTS":1}
    {"ACCOUNT_EXPIRY":["2024-03-18T00:00:00.000000000Z"],"WEEK":"2024-03-17T12:00:00.000000000Z","ACCOUNTS":1}