A time bucket is a fixed-width temporal interval that groups timestamped observations for analysis. DuckDB’s time_bucket() truncates timestamps to an interval and can align them with an optional offset, making inconsistent sensor intervals comparable. section 4.1
Aggregate sensor readings during ingestion
This query assigns readings to 15-minute buckets, replaces negative or missing power values with zero, averages each group, and inserts the result into readings. section 4.1
Listing 4.1 Cleaning and transforming data during ingestion
INSERT INTO readings(system_id, read_on, power)
SELECT any_value(SiteId), #1
time_bucket(
INTERVAL '15 Minutes',
CAST("Date-Time" AS timestamp)
) AS read_on, #2
avg(
CASE
WHEN ac_power < 0 OR ac_power IS NULL THEN 0
ELSE ac_power END) #3
FROM
read_csv_auto(
'https://developer.nrel.gov/api/pvdaq/v3/' ||
'data_file?api_key=DEMO_KEY&system_id=10&year=2019'
)
GROUP BY read_on
ORDER BY read_on;
Inspect the complete DuckDB SQL pattern for reading CSV data, casting timestamps, bucketing observations, cleaning values, grouping, and ordering results. section 4.1
Use buckets to:
- Normalize data arriving at different intervals, such as one-minute and five-minute measurements. section 4.1
- Remove already-ingested readings outside the intended quarter-hour boundaries with date_part and DELETE. section 3.4.3
