Skip to main content

SingleStoreDB v8.0

TIME_BUCKET

A function that normalizes time to the nearest bucket start time.

Syntax

TIME_BUCKET (bucket_width [, time [,origin]])

Arguments

  • bucket_width: time interval in either MySQL or ISO-8601 duration format. For example, INTERVAL 1 DAY, “1h4m”.

    Note

    SingleStoreDB only supports the following ISO-8601 bucket_width values: W/w (week), D/d (day), H/h (hour), M/m (minute), and S/s (second).

  • time: timestamp to be bucketed with either one of the following types: DATETIME, DATETIME(6), TIMESTAMP, TIMESTAMP(6). If no time expression is specified, then the SERIES TIMESTAMP is used for comparison. Only one SERIES TIMESTAMP can be used for comparison.

  • origin: (optional) timestamp/datetime that all buckets should be aligned relative to. Default value is 2000-01-03 00:00:00, Monday. For example, if you want to bucket by a seven day width, with the buckets starting at 8am Monday morning, pick an origin datetime value that is a Monday at 8am. The exact date does not matter, but it is a best practice to choose one shortly before the dates in your data set.

    Conceptually, TIME_BUCKET with an origin is calculated as: TIME_BUCKET(bucket_width, time, origin) = time - ((time - origin) % bucket_width)

Return Type

Returns a time/datetime value in the same format as the arguments.

Example

The following example uses the time series functions TIME_BUCKET(), FIRST(), and LAST() to create fictional candlestick charts that show the high, low, open, and close for a stock over time, bucketed by a one-day window:

CREATE TABLE trade (
  id INT, sym CHAR(4), ts DATETIME SERIES TIMESTAMP, price DECIMAL(8,2), shares FLOAT
);

INSERT trade VALUES(1, "MSFT", now(), 140.00, 100.00);
INSERT trade VALUES(2, "APPL", now() + interval 5 minute, 220.00, 10.00);
INSERT trade VALUES(3, "MSFT", now() + interval 10 minute, 141.00, 50.00);
INSERT trade VALUES(4, "APPL", now() + interval 15 minute, 223.00, 15.00);
INSERT trade VALUES(5, "APPL", now()  +interval 20 minute, 221.00, 20.00);
INSERT into trade
SELECT id + (SELECT MAX(id) FROM trade), sym, ts + interval 1 day, price * 1.01, shares
FROM trade;

SELECT * FROM trade ORDER BY id;
****
+------+------+---------------------+--------+--------+
| id   | sym  | ts                  | price  | shares |
+------+------+---------------------+--------+--------+
|    1 | MSFT | 2019-09-19 18:06:45 | 140.00 |    100 |
|    2 | APPL | 2019-09-19 18:11:45 | 220.00 |     10 |
|    3 | MSFT | 2019-09-19 18:16:45 | 141.00 |     50 |
|    4 | APPL | 2019-09-19 18:21:45 | 223.00 |     15 |
|    5 | APPL | 2019-09-19 18:26:45 | 221.00 |     20 |
|    6 | MSFT | 2019-09-20 18:06:45 | 141.40 |    100 |
|    7 | APPL | 2019-09-20 18:11:45 | 222.20 |     10 |
|    8 | MSFT | 2019-09-20 18:16:45 | 142.41 |     50 |
|    9 | APPL | 2019-09-20 18:21:45 | 225.23 |     15 |
|   10 | APPL | 2019-09-20 18:26:45 | 223.21 |     20 |
+------+------+---------------------+--------+--------+`

SELECT TIME_BUCKET('1d'), sym, MAX(price) as high, MIN(price) as low, FIRST(price) as open, LAST(price) as close 
FROM trade 
GROUP BY 1, 2 
ORDER BY 2, 1;
****
+----------------------------+------+--------+--------+--------+--------+
| TIME_BUCKET('1d')          | sym  | high   | low    | open   | close  |
+----------------------------+------+--------+--------+--------+--------+
| 2019-09-19 00:00:00.000000 | APPL | 223.00 | 220.00 | 220.00 | 221.00 |
| 2019-09-20 00:00:00.000000 | APPL | 225.23 | 222.20 | 222.20 | 223.21 |
| 2019-09-19 00:00:00.000000 | MSFT | 141.00 | 140.00 | 140.00 | 141.00 |
| 2019-09-20 00:00:00.000000 | MSFT | 142.41 | 141.40 | 141.40 | 142.41 |
+----------------------------+------+--------+--------+--------+--------+

The following are additional examples demonstrating how to use TIME_BUCKET:

CREATE TABLE tb_table (a INT, t DATETIME SERIES TIMESTAMP);
INSERT INTO tb_table VALUES (1, '2019-03-14 06:04:12'), (2, '2019-03-14 06:04:13');

SELECT TIME_BUCKET("1d", "2019-03-14 06:04:12");
****
+------------------------------------------+
| TIME_BUCKET("1d", "2019-03-14 06:04:12") |
+------------------------------------------+
| 2019-03-14 00:00:00                      |
+------------------------------------------+

SELECT TIME_BUCKET("1d", "2019-03-14 06:04:12", "2019-03-13 03:00:00");
****
+-----------------------------------------------------------------+
| TIME_BUCKET("1d", "2019-03-14 06:04:12", "2019-03-13 03:00:00") |
+-----------------------------------------------------------------+
| 2019-03-14 03:00:00                                             |
+-----------------------------------------------------------------+

SELECT TIME_BUCKET("1d"), FIRST(a) from tb_table GROUP BY 1;
****
+----------------------------+----------+
| TIME_BUCKET("1d")          | FIRST(a) |
+----------------------------+----------+
| 2019-03-14 00:00:00.000000 |        1 |
+----------------------------+----------+

DATE_TRUNC can be used to create time intervals larger than a week for the TIME_BUCKET function. Consider the tab5 table above for the following examples of bucketing by month, quarter, and year.

Month:

SELECT DATE_TRUNC('month', t) as MNT FROM tb_table;
****
+---------------------+
| MNT                 |
+---------------------+
| 2019-03-01 00:00:00 |
| 2019-03-01 00:00:00 |
+---------------------+

Quarter:

SELECT DATE_TRUNC('quarter', t) AS QTR FROM tb_table;
****
+---------------------+
| QTR                 |
+---------------------+
| 2019-01-01 00:00:00 |
| 2019-01-01 00:00:00 |
+---------------------+

Year:

SELECT DATE_TRUNC('year', t) as YR FROM tb_table;
****
+---------------------+
| YR                  |
+---------------------+
| 2019-01-01 00:00:00 |
| 2019-01-01 00:00:00 |
+---------------------+
In this section: