You can do all of that cleanly in DuckDB using one SQL pipeline: combine date+time → filter by ID → build hourly pivot.
I’ll assume your CSV has columns like:
date (e.g. 2026-06-28)
time (e.g. 14:35:10)
ID
Event
—
1. Combine date + time into a timestamp
DuckDB can safely combine them like this:
SELECT
*,
CAST(date || ‘ ‘ || time AS TIMESTAMP) AS dt
FROM ‘large.csv’;
This creates a proper datetime column dt.
—
2. Filter only ID = ‘C’
SELECT *
FROM (
SELECT *,
CAST(date || ‘ ‘ || time AS TIMESTAMP) AS dt
FROM ‘large.csv’
)
WHERE ID = ‘C’;
—
3. Create hourly field (this is the key step)
We truncate the timestamp to the hour:
DATE_TRUNC(‘hour’, dt) AS hour
So now we can group by hour.
—
4. Build hourly event counts (basic pivot-ready form)
This gives you a table like: hour | Event | count
SELECT
DATE_TRUNC(‘hour’, dt) AS hour,
Event,
COUNT(*) AS n
FROM (
SELECT *,
CAST(date || ‘ ‘ || time AS TIMESTAMP) AS dt
FROM ‘large.csv’
)
WHERE ID = ‘C’
GROUP BY 1, 2
ORDER BY 1, 2;
—
5. TRUE pivot (Events become columns)
If your Event values are known (e.g. “A”, “B”, “C”):
SELECT *
FROM (
SELECT
DATE_TRUNC(‘hour’, dt) AS hour,
Event
FROM (
SELECT *,
CAST(date || ‘ ‘ || time AS TIMESTAMP) AS dt
FROM ‘large.csv’
)
WHERE ID = ‘C’
)
PIVOT (
COUNT(*) FOR Event IN (‘A’, ‘B’, ‘C’)
)
ORDER BY hour;
—
6. Export to CSV (ready for Excel / LibreOffice / etc.)
COPY (
SELECT *
FROM (
SELECT *,
CAST(date || ‘ ‘ || time AS TIMESTAMP) AS dt
FROM ‘large.csv’
)
WHERE ID = ‘C’
)
TO ‘ID_C_hourly.csv’
(HEADER, DELIMITER ‘,’);
—
If your data format differs
Common tweaks:
If date/time are already strings like:
2026-06-28
14:35:10
This works:
CAST(date || ‘ ‘ || time AS TIMESTAMP)
If timestamps are messy:
Use:
strptime(date || ‘ ‘ || time, ‘%Y-%m-%d %H:%M:%S’)
—
If you want the next upgrade
Given your SDR / astronomy work, the next step up is:
rolling hourly rate plots
event spectra vs time
heatmaps (hour × event)
Say the word and I can turn this into a one-command pipeline or a saved DuckDB view.
Good — I’ll give you both a clean reusable pipeline and a single “one-shot” command style workflow in DuckDB.
Assume columns:
date = YYYY-MM-DD
time = HH:MM:SS
ID
Event
—
1) CLEAN REUSABLE PIPELINE (best approach)
Step A — Create a reusable view (combines date + time once)
CREATE VIEW base AS
SELECT
*,
strptime(date || ‘ ‘ || time, ‘%Y-%m-%d %H:%M:%S’) AS dt
FROM ‘large.csv’;
Now everything uses dt.
—
Step B — Filter to ID = C + hourly aggregation
CREATE VIEW id_c_hourly AS
SELECT
date_trunc(‘hour’, dt) AS hour,
Event,
COUNT(*) AS n
FROM base
WHERE ID = ‘C’
GROUP BY 1, 2;
—
Step C — Pivot (Events → columns)
If you know event names:
CREATE VIEW id_c_pivot AS
SELECT *
FROM id_c_hourly
PIVOT (
SUM(n) FOR Event IN (‘A’, ‘B’, ‘C’)
)
ORDER BY hour;
—
Step D — Export final table
COPY id_c_pivot
TO ‘ID_C_hourly_pivot.csv’
(HEADER);
—
2) ONE-SHOT PIPELINE (no views, all in one go)
Hourly pivot directly from CSV
COPY (
SELECT *
FROM (
SELECT
date_trunc(‘hour’, dt) AS hour,
Event
FROM (
SELECT *,
strptime(date || ‘ ‘ || time, ‘%Y-%m-%d %H:%M:%S’) AS dt
FROM ‘large.csv’
)
WHERE ID = ‘C’
)
PIVOT (
COUNT(*) FOR Event IN (‘A’, ‘B’, ‘C’)
)
ORDER BY hour
)
TO ‘ID_C_hourly_pivot.csv’
(HEADER);
—
3) OPTIONAL: MORE POWERFUL VERSION (recommended for real data)
Instead of hardcoding event names:
Event list auto-discovery + dynamic pivot
SELECT DISTINCT Event FROM ‘large.csv’ WHERE ID = ‘C’;
Then use those values in the IN (…) list.
—
4) Useful upgrade for your kind of data (radio/SDR style)
If your “Event” is actually signal detections:
Heatmap-style table (hour × event intensity)
SELECT
date_trunc(‘hour’, dt) AS hour,
Event,
COUNT(*) AS intensity
FROM base
WHERE ID = ‘C’
GROUP BY 1, 2
ORDER BY 1;
This is often better than pivot for plotting in Python / Excel.