hour_at runs 2023-01-01 to 2023-12-31

Coverage note for grid-energy-load, which is the thing to check first and the thing everyone checks last.

2023-01-01 00:00:00 to 2023-12-31 23:00:00. That is 365 calendar days, of which 365 have at least one row — so the series is dense. Average 24.0 rows per populated day.

select date_trunc('day', hour_at) as day, count(*) as rows
from grid_energy_load
group by 1
order by 1;

A dense series means a plain group by day is safe. That is worth confirming rather than assuming — the moment a day drops out, every window function that counts rows instead of days starts comparing the wrong pair, and a seven-day lag silently becomes an eight-day one.

The other number worth having before you start: 24.0 rows per populated day tells you what granularity the data can actually support. Aggregating to something finer than the data is dense enough to fill produces a chart of noise, and there is no warning — the query returns, the line is drawn, and the wobble gets interpreted.

Because this is a timestamp rather than a date, every daily aggregate embeds a time zone decision. date_trunc('day', hour_at) truncates in whatever zone the session is set to, so the same query returns different daily totals for two people in different offices. Truncating explicitly in UTC and converting for display is the version that does not produce meetings.

A date range is a fact about the data, not about the question. Filters outside it return empty results, filters half-inside it return partial ones, and neither raises anything. Check the range, then write the filter — and use a half-open interval when you do, because between on a timestamp includes exactly one instant of the final day.