Modelling Sensor Deployment History with Validity Intervals #

A validity interval turns a registry row from a statement about the present into a statement about a period: this device sat at this coordinate, with these coefficients, from this instant until that one. Once every mutable fact carries one, joining a reading to its metadata stops being a lookup and becomes an as-of join — find the row whose interval contains the observation time. This page builds that model end to end: the half-open interval convention, the PostgreSQL schema that makes overlaps impossible, the SQL join, and the pandas equivalent for backfills. It is the mechanical core of sensor metadata and device registry management.

Why the Interval Convention Decides Everything Else #

Pick the wrong convention and every downstream query inherits an off-by-one that appears only at change boundaries — which is precisely where anyone auditing the data will look.

Half-open means [valid_from, valid_to): the start instant is inside the interval, the end instant is not. When a sensor is moved at 2026-03-14T09:00:00Z, the old deployment’s valid_to and the new deployment’s valid_from are both exactly that instant. A reading at 08:59:59.999 joins to the old row; one at 09:00:00.000 joins to the new one; nothing joins to both and nothing falls between them. Closed-closed intervals (valid_to inclusive) duplicate the boundary instant. Open-closed intervals lose it. Both bugs are rare enough to survive testing and common enough to appear in production.

There is a second reason the convention matters: PostgreSQL’s tstzrange is half-open by default ([)), and so is pandas.merge_asof’s backward direction. Choosing anything else means fighting both tools forever.

Production-Ready Implementation #

The schema puts the interval in a range type so the database can enforce non-overlap directly.

-- postgresql 15 · btree_gist extension required
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE deployment (
    deployment_id bigserial PRIMARY KEY,
    device_id     text        NOT NULL,
    site_id       text        NOT NULL,
    latitude      double precision NOT NULL,
    longitude     double precision NOT NULL,
    height_agl_m  real        NOT NULL,
    valid         tstzrange   NOT NULL,
    CONSTRAINT deployment_no_overlap
        EXCLUDE USING gist (device_id WITH =, valid WITH &&)
);

CREATE INDEX deployment_lookup ON deployment USING gist (device_id, valid);

EXCLUDE USING gist (device_id WITH =, valid WITH &&) reads as: no two rows may share a device_id and have overlapping valid ranges. It is one line, and it removes an entire class of data-quality incident — the kind that shows up as a doubled reading count in a monthly report six weeks later.

The as-of join is then a containment test, which the GiST index answers directly:

SELECT r.device_id,
       r.observed_at,
       r.value,
       d.site_id,
       d.latitude,
       d.longitude,
       d.height_agl_m
FROM   reading r
LEFT   JOIN deployment d
       ON  d.device_id = r.device_id
       AND d.valid @> r.observed_at        -- range contains timestamp
WHERE  r.observed_at >= %(since)s
  AND  r.observed_at <  %(until)s;

The LEFT JOIN is deliberate. An inner join makes readings from unregistered devices vanish from the result set with no trace, which is the worst possible handling: the data is neither processed nor reported missing. With a left join, site_id IS NULL is a countable, alertable condition.

For backfills, the pandas equivalent uses merge_asof, which does the same thing in one pass over two sorted frames:

# python 3.11 · pandas==2.2.2
import pandas as pd

def attach_deployment(readings: pd.DataFrame, deployments: pd.DataFrame) -> pd.DataFrame:
    """As-of join readings to the deployment valid at each observation time.

    readings:    device_id, observed_at (UTC, tz-aware), value
    deployments: device_id, valid_from, valid_to (UTC, tz-aware; valid_to NaT = current)
    Returns readings with site_id/latitude/longitude/height_agl_m attached, NaN where
    no deployment covered the observation.
    """
    r = readings.sort_values("observed_at")
    d = deployments.sort_values("valid_from")

    merged = pd.merge_asof(
        r, d,
        left_on="observed_at", right_on="valid_from",
        by="device_id",
        direction="backward",          # the most recent interval that started at or before
        allow_exact_matches=True,      # valid_from is inclusive
    )

    # merge_asof only checks the lower bound; enforce the exclusive upper bound ourselves
    expired = merged["valid_to"].notna() & (merged["observed_at"] >= merged["valid_to"])
    merged.loc[expired, ["site_id", "latitude", "longitude", "height_agl_m"]] = pd.NA
    return merged

The comment on the last two lines is the part people miss. merge_asof matches on the lower bound only — it will happily attach a deployment that ended a year before the reading was taken. The explicit upper-bound mask is what turns a nearest-match into a containment match.

Parameter Tuning Guide #

The right-hand column is the one to plan around. A location correction invalidates every spatial aggregate in its interval; a project change invalidates a report header. Treat them differently in your reprocessing policy rather than rebuilding everything on any registry edit.

Verification and Testing #

Three tests catch nearly every interval bug. The boundary test is the one that matters most.

# python 3.11 · pandas==2.2.2
import pandas as pd

def test_boundary_instant_joins_exactly_once():
    move = pd.Timestamp("2026-03-14T09:00:00Z")
    deployments = pd.DataFrame({
        "device_id": ["s1", "s1"],
        "site_id": ["roof-a", "roof-b"],
        "latitude": [51.50, 51.52], "longitude": [-0.12, -0.10],
        "height_agl_m": [12.0, 18.0],
        "valid_from": [pd.Timestamp("2026-01-01T00:00:00Z"), move],
        "valid_to": [move, pd.NaT],
    })
    readings = pd.DataFrame({
        "device_id": ["s1", "s1", "s1"],
        "observed_at": [move - pd.Timedelta("1ms"), move, move + pd.Timedelta("1ms")],
        "value": [21.0, 21.1, 21.2],
    })

    out = attach_deployment(readings, deployments)
    assert len(out) == 3                              # no duplication at the boundary
    assert list(out["site_id"]) == ["roof-a", "roof-b", "roof-b"]

Alongside it, assert that the database rejects an overlap (insert a deliberately overlapping deployment and expect an ExclusionViolation), and that a reading before any deployment resolves to null rather than to the earliest row. The third is the one merge_asof gets wrong if direction is set to nearest instead of backward — a setting that looks more forgiving and is quietly catastrophic, because it attaches future metadata to past readings.

Gotchas #

merge_asof requires both frames sorted by the join key, globally. Sorting within groups is not enough; the function raises if the left frame is unsorted, but silently produces wrong matches if the right frame’s valid_from is not monotonic. Sort both, every time.

Timezone-naive columns will not merge against timezone-aware ones. pandas raises here, which is merciful. The dangerous version is a registry loaded from CSV where valid_from parses as naive and is then localized to the server’s zone rather than UTC — the join succeeds and is wrong by the UTC offset.

A GiST index on (device_id, valid) is not used by an equality-only query. If you also query the deployment table by device_id alone, add a plain B-tree index for that path. The GiST index is for containment, and the planner will not use it to answer WHERE device_id = 's1' efficiently.

Deployments with valid_to in the future. Scheduling a move in advance is reasonable, but the exclusion constraint will then reject the row that closes the current deployment. Close first, insert second, inside one transaction.

FAQ #

Related #

• Sensor Metadata and Device Registry Management — the registry model this join reads from
• Validating Sensor Payloads with Pydantic — the guard that runs before the join, so only well-formed readings reach it
• Handling Timezone Drift in High-Frequency IoT Streams — why the timestamps on both sides of this join have to be UTC