What reading_at actually contains in sensor-telemetry

Working through sensor-telemetry again. reading_at is the column people trip over, so here is what it actually looks like.

8,000 rows, no nulls, 8,000 distinct values. Reading time, UTC. Irregular spacing.

It runs from 2023-01-01 00:10:00 to 2023-02-28 19:20:00, covering 59 distinct days.

It is a timestamp, not a date, which means every comparison against a bare date is a comparison against midnight. reading_at <= date '2023-06-30' silently excludes almost all of 30 June. Half-open ranges — >= start and < end — avoid the whole class of off-by-one-day bugs and read no worse.

select
  count(*)                     as rows,
  count(reading_at)            as present,
  count(*) - count(reading_at) as nulls,
  count(distinct reading_at)   as distinct_values
from sensor_telemetry;

Check the range before filtering on it. Half the "the dashboard is empty" reports we have seen are a date filter outside the data's actual range, and the query is not wrong so nothing errors.

Where this bites: 59 populated days is what any window function over this column has to work with. A seven-day lag counts rows, not days — so if a day is missing, lag(7) quietly compares against eight days ago and the week-over-week number is wrong in a way that looks plausible.

The grain is one row per sensor reading, which is the context every one of those numbers depends on. None of them survive a change of grain, which is why "profile the column" and "profile the table" are the same job. Full schema, and the CSV, on the dataset page.