Between Times Check Examples

The situation: A quarterly reporting table is rebuilt from the orders feed. Every row loaded into it must carry an o_orderdate inside the 2026 first quarter, from 2026-01-01 00:00 UTC through 2026-03-31 23:59:59 UTC. Anything outside that window means the extract pulled the wrong slice.

Check configuration

Payload

{
    "description": "Order dates must fall inside the 2026 first quarter.",
    "rule": "betweenTimes",
    "fields": ["o_orderdate"],
    "container_id": 318,
    "coverage": 1,
    "filter": null,
    "properties": {
        "min_time": "2026-01-01T00:00:00Z",
        "max_time": "2026-03-31T23:59:59Z"
    },
    "tags": ["reporting", "period"],
    "additional_metadata": {"jira": "DATA-6401"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}

Sample Data

What gets flagged

Order 5003 is dated in April, outside the quarter. Orders 5001 and 5004 sit exactly on the two boundaries and pass, because both edges are inclusive. Coverage is 100%, so the failing row is reported as a Record Anomaly.

Flowchart

graph TD
    A["No filter, evaluate all rows"] --> B["Interpret o_orderdate as timestamp"]
    B --> C{"Is o_orderdate >= Min<br/>AND <= Max?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E["Flag row.<br/>Record Anomaly per failing row."]

Equivalent SQL

-- Rows the Between Times check would flag.
SELECT o.*
FROM orders_q1 o
WHERE o.o_orderdate IS NOT NULL
  AND NOT (o.o_orderdate >= TIMESTAMP '2026-01-01 00:00:00 UTC'
       AND o.o_orderdate <= TIMESTAMP '2026-03-31 23:59:59 UTC');

The situation: Badge events for the night shift must be stamped inside the shift window, from 2026-05-10 22:00 UTC through 2026-05-11 06:00 UTC. Day-shift events live in the same table and are out of scope for this check.

Check configuration

Payload

{
    "description": "Night-shift badge events must fall inside the shift window.",
    "rule": "betweenTimes",
    "fields": ["event_at"],
    "container_id": 826,
    "coverage": 1,
    "filter": "shift = 'night'",
    "properties": {
        "min_time": "2026-05-10T22:00:00Z",
        "max_time": "2026-05-11T06:00:00Z"
    },
    "tags": ["workforce", "period"],
    "additional_metadata": {"jira": "DATA-6455"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 33
}

Sample Data (filtered to shift = 'night')

Why the filter matters

The filter runs before the comparison, so day-shift events are never tested against the night window. Only night-shift rows are evaluated.

What gets flagged

E-702 was stamped at 07:12, after the shift ended, so it is the only row reported. Coverage is 100%, so the failure is reported as a Record Anomaly, and the message ends with the filter that scoped the evaluation.

Flowchart

graph TD
    A["Apply filter: shift = 'night'"] --> B["Interpret event_at as timestamp"]
    B --> C{"Is event_at >= Min<br/>AND <= Max?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E["Flag row.<br/>Anomaly message ends with<br/>[filter: shift = 'night']"]

Equivalent SQL

-- Rows the check would flag on the night shift.
SELECT b.*
FROM badge_events b
WHERE b.shift = 'night'
  AND b.event_at IS NOT NULL
  AND NOT (b.event_at >= TIMESTAMP '2026-05-10 22:00:00 UTC'
       AND b.event_at <= TIMESTAMP '2026-05-11 06:00:00 UTC');

The situation: An activity log should only hold events from the period the platform has existed, between 2015-01-01 00:00 UTC and 2026-12-31 23:59:59 UTC. A small fraction of rows carries dates from a broken importer and is being cleaned up, so the check tolerates up to 0.5% failures. Rows whose date failed to parse upstream are NULL.

Check configuration

Payload

{
    "description": "Activity dates must fall inside the platform's lifetime.",
    "rule": "betweenTimes",
    "fields": ["activity_at"],
    "container_id": 904,
    "coverage": 0.995,
    "filter": null,
    "properties": {
        "min_time": "2015-01-01T00:00:00Z",
        "max_time": "2026-12-31T23:59:59Z"
    },
    "tags": ["plausibility", "activity"],
    "additional_metadata": {"jira": "DATA-6502"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 48
}

Sample Data

What gets flagged

Activities A-9002 and A-9003 carry an epoch date and a far-future date, both from the broken importer. A-9004 is NULL, so it passes without firing an anomaly but still counts in the scanned total. Only 50% of the rows pass, which is below the 99.5% coverage threshold, so the check reports a single Shape Anomaly for the dataset with a sample of the offending rows. Coverage below 100% does not produce Record Anomalies.

Flowchart

graph TD
    A["No filter, evaluate all rows"] --> B["Interpret activity_at as timestamp"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Is activity_at >= Min<br/>AND <= Max?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Passing rate falls below the 99.5%<br/>coverage, so one Shape Anomaly is reported."]

Equivalent SQL

-- Rows the check would flag.
SELECT a.*
FROM activity_log a
WHERE a.activity_at IS NOT NULL
  AND NOT (a.activity_at >= TIMESTAMP '2015-01-01 00:00:00 UTC'
       AND a.activity_at <= TIMESTAMP '2026-12-31 23:59:59 UTC');