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 theSERIES TIMESTAMP
is used for comparison. Only oneSERIES 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 | +---------------------+