Where Did the Quiet Day Go?

Botie groups recorded incidents by day and draws a chart. Monday has two incidents and Wednesday has one. Tuesday disappears entirely, which makes the chart look as though Wednesday immediately followed Monday.

The event table records things that happened. It cannot supply a row for a day when nothing happened. We need a calendar to define the days we intend to report, plus a precise rule for assigning each event to one day.

Read each event’s UTC day, count events by day, and left join those counts onto the requested calendar.

[
  {
    "day": "2026-01-01",
    "n": 2
  },
  {
    "day": "2026-01-02",
    "n": 0
  },
  {
    "day": "2026-01-03",
    "n": 1
  }
]

These fixtures store UTC timestamps as fixed-width YYYY-MM-DD HH:MM:SS text. In that one representation, text order follows chronological order. Mixed offsets, inconsistent widths or a mixture of seconds and milliseconds would invalidate that shortcut.

Store or validate the format at ingestion, and document the unit for numeric timestamps. A column named occurred_at does not by itself guarantee a timezone or a unit.

SQLite’s date functions support particular representations. They are not a substitute for deciding what the source field means.

We assign an event to a day when its timestamp is at least the start of that day and strictly less than the start of the next day. This half-open interval includes the starting midnight and excludes the next midnight.

BETWEEN includes both endpoints. Using it with the two midnights would count a boundary event in both adjacent days. Guessing a final instant such as 23:59:59 also ties the query to a precision that may change.

Use the next interval’s start as the exclusive upper bound.

CREATE TABLE calendar(day TEXT PRIMARY KEY NOT NULL);
CREATE TABLE events(event_id INTEGER PRIMARY KEY, occurred_at TEXT NOT NULL);
INSERT INTO calendar VALUES ('2026-01-01'),('2026-01-02'),('2026-01-03');
INSERT INTO events VALUES (1,'2026-01-01 08:00:00'),(2,'2026-01-01 23:59:59'),(3,'2026-01-03 00:00:00');

SELECT event_id FROM events
WHERE occurred_at >= '2026-01-01 00:00:00'
  AND occurred_at < '2026-01-02 00:00:00'
ORDER BY event_id;

The calendar table lists the days we want, including those with no event. LEFT JOIN keeps those calendar rows. Put the event-time conditions in ON so an unmatched day still survives.

Count the non-NULL event_id, not COUNT(*). A left join supplies one placeholder row for an unmatched day; COUNT(*) would incorrectly turn that placeholder into one incident.

The fixture’s event_id is always known on a real event, so it is a reliable counted match.

CREATE TABLE calendar(day TEXT PRIMARY KEY NOT NULL);
CREATE TABLE events(event_id INTEGER PRIMARY KEY, occurred_at TEXT NOT NULL);
INSERT INTO calendar VALUES ('2026-01-01'),('2026-01-02'),('2026-01-03');
INSERT INTO events VALUES (1,'2026-01-01 08:00:00'),(2,'2026-01-01 23:59:59'),(3,'2026-01-03 00:00:00');

SELECT c.day, e.event_id
FROM calendar AS c LEFT JOIN events AS e
  ON e.occurred_at >= c.day || ' 00:00:00'
 AND e.occurred_at < datetime(c.day, '+1 day')
ORDER BY c.day, e.event_id;

A calendar left-joined to precomputed daily counts also works. In that arrangement, an absent count is NULL and COALESCE can deliberately convert it to zero. That is the arrangement used by the workshop.

This assumes the log is complete enough for “no recorded events” to be useful. If the sensor system was offline, zero records does not prove zero real incidents. Keep ingestion coverage or service status separately when the distinction matters.

Replacing missing data with zero is a statement about the report’s meaning, not a general cleanup rule.

Our day boundary is midnight UTC. A maintenance team may instead ask for days in a named timezone or shifts beginning at 07:00. Those are different boundaries and can cross UTC dates.

Use a timezone-aware system to construct the intended interval boundaries. Daylight-saving changes can make a local day shorter or longer than twenty-four hours. SQLite’s host-local conversion is not a general named-timezone database for arbitrary sites.

Once the correct UTC boundaries are known, the same inclusive-start/exclusive-end comparison can assign events consistently.

For elapsed seconds in this second-precision fixture, subtract unixepoch values. A calendar label alone is not an elapsed duration, and months are not a fixed number of seconds.

For fractional precision, choose a representation and conversion that preserve the required resolution. Functions available in newer SQLite releases may not exist in the browser’s runtime; these examples use functions supported by the lesson environment.

Round only for display when the underlying precision matters.

SELECT unixepoch('2026-01-03 09:30:00') - unixepoch('2026-01-03 08:00:00') AS seconds;

Insert an event exactly at the next midnight. It must enter the next day only. Add an event outside the requested calendar range and verify that no extra report day appears. Remove every event and verify that every requested calendar day still appears with zero.

These cases catch reporting mistakes that a busy day full of ordinary timestamps can hide.

The requested calendar is part of the query input. Do not derive it only from days that happened to have records.