Is there a way to do segmenttation aggregation in duckdb?

Question

I want to store timeseries data in duckdb, and query them for displaying in the website, for this circumstance, data needs to be aggregated and calculated at certain time intervals, I noticed in IotDB's documentation, it could provide mechanism like:

select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01T00:00:00, 2017-11-07T23:00:00),1d);

The precise need for this query is to split the dataset as several time-based sections, and for each time section, an aggregation query would be executed.

For example, I want to query the data from last 7 days, and the provider would produce data every 15 seconds, so the timeline would be quite large for the website to display, it would be better to aggregate the data for average to 120 seconds or longer to shorten the chart's x axis.

So is there a proper way to implement it in DuckDB?

Answer 1

You don't give a time field for the aggregation, so I assume it is implied.

I think the query you want would filter and bin the time values by day and aggregate the other fields:

select date_trunc('day', "timestamp") as "day", count(status), max(temperature)
from root.ln.wf01.wt01
group by 1
where "timestamp" between '2017-11-01T00:00:00'::TIMESTAMP
                      and '2017-11-07T23:00:00'::TIMESTAMP