# Help: `bitemporal` (concept)

A bi-temporal model tracks two independent time axes: **valid time** (when the event occurred in the real world) and **transaction time** (when it was recorded in the system). Filtering on both axes lets you reconstruct what any report would have shown on any past date.

## Pattern: filter on both valid time and recorded time

```featureql
-- valid time: tx_date scopes to the period of interest
IS_Q1 := TX_DATE >= DATE '2024-01-01' AND TX_DATE < DATE '2024-04-01',
-- transaction time: recorded_at limits to what was known by the cutoff
KNOWN_AS_OF := TX_RECORDED_AT <= TIMESTAMP '2024-03-31 23:59:59',
RETURN
    TOTAL := SUM(TX_AMOUNT)
...
WHERE IS_Q1 AND KNOWN_AS_OF
```

Runnable snippet: [`help_bitemporal_asof`](/llms/help_bitemporal_asof.md)

## What changes when the cutoff shifts

A transaction recorded after the knowledge cutoff is excluded from the earlier report and included in the later one — even though its valid-time date falls in the same period:

```featureql
-- As-of March 31: tx recorded Apr 2 is excluded → total = 56000
KNOWN_AS_OF_MAR := TX_RECORDED_AT <= TIMESTAMP '2024-03-31 23:59:59',

-- As-of April 30: same tx is now included (adjustment of -1000) → total = 55000
KNOWN_AS_OF_APR := TX_RECORDED_AT <= TIMESTAMP '2024-04-30 23:59:59',
```

Runnable snippet: [`help_bitemporal_correction`](/llms/help_bitemporal_correction.md)

## Persist the cutoff so consumers cannot omit it

A query-local `KNOWN_AS_OF` is easy to drop after `CREATE FEATURES`. Do not persist the raw amount. Make the measure depend on `AS_OF := INPUT(TIMESTAMP)` and leave the extract unpublished. `FOR` then requires the cutoff:

```featureql
AS_OF := INPUT(TIMESTAMP),
-- hide SRC[amount]; only the cutoff-aware measure is selectable
AMOUNT_AS_OF := IF(TX_RECORDED_AT <= AS_OF, TX_AMOUNT, 0),
RETURN TOTAL := SUM(AMOUNT_AS_OF)
FOR
    TX_ID := BIND_COLUMNS(...),
    AS_OF := BIND_VALUE(TIMESTAMP '2024-03-31 23:59:59'),
```

Runnable: [`help_bitemporal_asof_bound`](/llms/help_bitemporal_asof_bound.md). Omit `AS_OF` → `UE/NOT-VALID-FOR-EVALUATION` ([`help_bitemporal_asof_unbound`](/llms/help_bitemporal_asof_unbound.md)).

Do not mark the raw amount `NOT ADDITIVE ACROSS (recorded_at)`. That is a collapse prohibition and blocks the bounded `SUM` you want.

`DATE#` / `TIMESTAMP#` accept `<=` directly. Keep recorded time as a plain `TIMESTAMP` unless you need the entity for something else.

## Detecting late-recorded events

Flag transactions recorded more than N days after their valid date, and identify which reporting period closes they missed:

```featureql
DAYS_LATE := DATE_SUBTRACT(TX_RECORDED_DATE::TIMESTAMP, TX_DATE::TIMESTAMP, 'day'),
-- Build an array of period-end dates and filter to those between tx_date and recorded_date
CLOSE_DATES := ZIP(ARRAY[DATE '2024-01-31', DATE '2024-02-29', DATE '2024-03-31', ...] AS close_date),
CLOSE_WITH_TX := CARRY(TX_DATE AS td, TX_RECORDED_DATE AS rd INTO CLOSE_DATES),
MISSED := CLOSE_WITH_TX.TRANSFORM(
    RETURN DATE_FORMAT(close_date::TIMESTAMP, '%Y-%m') AS close_month
    WHERE close_date >= td AND close_date < rd
    ORDER BY close_date
),
MISSED_CLOSES := ARRAY_JOIN(UNWRAP(MISSED), ', '),
IS_LATE := DAYS_LATE > 1,
```

## Key insight

`@eval_as_literal(CURRENT_DATE())` gives a single consistent snapshot timestamp for the whole query. Without it, `CURRENT_DATE()` is evaluated per row and reports may vary mid-execution.

# Help: `bitemporal` (snippet)

## `help_bitemp_setup`

```slt
statement ok
--
CREATE SCHEMA IF NOT EXISTS help_bitemp_bench;
--
DROP TABLE IF EXISTS help_bitemp_bench.transactions;
--
CREATE TABLE help_bitemp_bench.transactions (
    id          BIGINT,
    customer_id BIGINT,
    amount      BIGINT,
    tx_date     DATE,
    recorded_at TIMESTAMP
);
--
-- tx 1-4: normal; tx 5: late correction (recorded 35 days after event date)
INSERT INTO help_bitemp_bench.transactions VALUES
    (1, 100,  5000, DATE '2024-01-15', TIMESTAMP '2024-01-15 10:00:00'),
    (2, 100,  8000, DATE '2024-03-01', TIMESTAMP '2024-03-01 09:00:00'),
    (3, 200,  3000, DATE '2024-02-20', TIMESTAMP '2024-02-20 11:00:00'),
    (4, 100,  7500, DATE '2024-03-10', TIMESTAMP '2024-03-10 14:00:00'),
    (5, 100, -1000, DATE '2024-02-28', TIMESTAMP '2024-04-05 15:00:00');
--
SELECT 1 AS ok
```

## `help_bitemporal_asof`

```slt
# depends: help_bitemp_setup
# schema: BIGINT
query TOTAL_Q1_AS_OF_MAR31:I
DEFINE
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM help_bitemp_bench.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-03-31 23:59:59'
RETURN
    total_q1_as_of_mar31 := SUM(tx_amount) FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(help_bitemp_bench.transactions)
    )
;
----
23500
```

## `help_bitemporal_correction`

```slt
# depends: help_bitemp_setup
# schema: BIGINT
query TOTAL_Q1_AS_OF_APR30:I
DEFINE
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM help_bitemp_bench.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-04-30 23:59:59'
RETURN
    total_q1_as_of_apr30 := SUM(tx_amount) FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(help_bitemp_bench.transactions)
    )
;
----
22500
```

## `help_bitemporal_asof_bound`

```slt
# comment: AS_OF as INPUT makes the cutoff mandatory in FOR. Mar 31 excludes the Apr 5 correction.
# depends: help_bitemp_setup
# tags: bitemporal, persistence
# schema: BIGINT
query TOTAL_Q1_AS_OF_MAR31:I
DEFINE
    tx_id := INPUT(BIGINT),
    as_of := INPUT(TIMESTAMP),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM help_bitemp_bench.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    amount_as_of := IF(tx_recorded_at <= as_of, tx_amount, 0)
RETURN
    total_q1_as_of_mar31 := SUM(amount_as_of) FILTER (WHERE is_q1)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(help_bitemp_bench.transactions)
    ),
    as_of := BIND_VALUE(TIMESTAMP '2024-03-31 23:59:59')
;
----
23500
```

## `help_bitemporal_asof_unbound`

```slt
# comment: Forgetting AS_OF is refused because the measure depends on it.
# depends: help_bitemp_setup
# tags: bitemporal, persistence
# no-strict: query is meant to raise an error
# no-schema:
query error
DEFINE
    TX_ID := INPUT(BIGINT),
    AS_OF := INPUT(TIMESTAMP),
    TX_SOURCE := EXTERNAL_COLUMNS(
        tx_id       BIGINT BIND TO TX_ID,
        amount      BIGINT,
        tx_date     DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM help_bitemp_bench.transactions)
    ),
    TX_AMOUNT      := TX_SOURCE[amount],
    TX_DATE        := TX_SOURCE[tx_date],
    TX_RECORDED_AT := TX_SOURCE[recorded_at],
    AMOUNT_AS_OF   := IF(TX_RECORDED_AT <= AS_OF, TX_AMOUNT, 0),
RETURN TOTAL := SUM(AMOUNT_AS_OF)
FOR
    TX_ID := BIND_COLUMNS(id FROM TABLE(help_bitemp_bench.transactions))
----
ERROR UE/NOT-VALID-FOR-EVALUATION
```

**Related tags**: [`scd`](/llms/scd.md) [`point_in_time`](/llms/event_accumulation.md) [`sliding_window`](/llms/sliding_window.md) [`running_balance`](/llms/event_sourced_state.md) [`transform_carry`](/llms/transform_carry.md)