TIME_BUCKET

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

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

SingleStore 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:

SQL

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

SQL

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;

SQL

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 |
+------+------+---------------------+--------+--------+`

SQL

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:

SQL

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');

SQL

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                      |
+------------------------------------------+

SQL

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                                             |
+-----------------------------------------------------------------+

SQL

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:

SQL

SELECT DATE_TRUNC('month', t) as MNT FROM tb_table;

+---------------------+
| MNT                 |
+---------------------+
| 2019-03-01 00:00:00 |
| 2019-03-01 00:00:00 |
+---------------------+

Quarter:

SQL

SELECT DATE_TRUNC('quarter', t) AS QTR FROM tb_table;

+---------------------+
| QTR                 |
+---------------------+
| 2019-01-01 00:00:00 |
| 2019-01-01 00:00:00 |
+---------------------+

Year:

SQL

SELECT DATE_TRUNC('year', t) as YR FROM tb_table;

+---------------------+
| YR                  |
+---------------------+
| 2019-01-01 00:00:00 |
| 2019-01-01 00:00:00 |
+---------------------+