Grouping by 10 Minute Bars in DuckDB

How do I round time to the nearest X minutes in DuckDB?

Candlestick charts and other common queries in finance require bucketing time-series data in time buckets. We will look at grouping on time for example 5 minutes, 15 minutes in DuckDB:

Timestamp Column Grouping

With timestamp based columns, grouping by time buckets is easy as we can use either datetrunc to round to whole units or time_bucket to specify bucket sizes:

CREATE OR REPLACE TABLE goldbtc AS SELECT *,time::Time AS ttime 
		FROM READ_PARQUET('https://www.timestored.com/data/sample/pq/gold_vs_bitcoin.parquet'); 

SELECT datetrunc('minute', "time")  AS time,MIN("gold") as gold FROM goldbtc GROUP BY ALL ORDER BY time;  	

SELECT  time_bucket(INTERVAL '2 minutes', "time") AS time,MIN("gold") as gold FROM goldbtc GROUP BY ALL ORDER BY time;

time	gold
2024-10-28T22:20:00.000000	0.7250
2024-10-28T22:22:00.000000	0.6109
2024-10-28T22:24:00.000000	0.6350
2024-10-28T22:26:00.000000	0.3238
2024-10-28T22:28:00.000000	0.3005

Time Column Buckets

Time has less functions available within duckdb, so we need to create our own xbar:

CREATE OR REPLACE TABLE goldbtc AS SELECT *,time::Time AS ttime 
		FROM READ_PARQUET('https://www.timestored.com/data/sample/pq/gold_vs_bitcoin.parquet'); 

CREATE OR REPLACE function xbar(n,x) AS n*(x//n);

CREATE OR REPLACE function xbarTime(n,x) AS 
    n*(((date_part('hour', x::Time)*60) + date_part('minute', x::Time))//n);

SELECT MIN("time") AS time,MIN("gold") as gold FROM goldbtc GROUP BY xbarTime(2,"ttime") ORDER BY time;

time	gold
2024-10-28T22:20:00.000000	0.7250
2024-10-28T22:22:00.000000	0.6109
2024-10-28T22:24:00.000000	0.6350
2024-10-28T22:26:00.000000	0.3238
2024-10-28T22:28:00.000000	0.3005

Grouping by 10 Minute Bars in DuckDB

How do I round time to the nearest X minutes in DuckDB?

Timestamp Column Grouping

Time Column Buckets

See Also