ACCUMULATION_AT_TIME()

All functions > BUSINESS > ACCUMULATION_AT_TIME()

Returns the state of an accumulation of events at the given point in time.

Signatures

Returns: Array of event records at point in time

ACCUMULATION_AT_TIME(accumulation: ARRAY<T>, point_in_time: TIMESTAMP) → ARRAY<T>

With:

• T : Event record: ROW<time_create: TIMESTAMP, time_update: TIMESTAMP, time_delete: TIMESTAMP, ...>

Notes

• Accumulation tracks records that exist during time ranges
• Each record has time_create (when created) and time_delete (when deleted, NULL if still active)
• Returns all records where point_in_time is between time_create and time_delete
• For records with SCD fields, resolves those fields to their values at point_in_time
• Useful for temporal tables, event logs, and tracking entity states over time
• Supports nested SCD values within accumulation records
• Returns empty array if no records were active at the specified time

Examples

SELECT
    f1 := ACCUMULATION_AT_TIME(ARRAY[ROW(10 AS order_id, TIMESTAMP '2022-05-05 15:52:06' AS time_create, TIMESTAMP '2022-05-05 15:52:06' AS time_update, NULL(TIMESTAMP) AS time_delete)], TIMESTAMP '2022-05-06 15:50:00')[order_id], -- Order ids active at the reference time
    f2 := ACCUMULATION_AT_TIME(ARRAY[ROW(10 AS order_id, TIMESTAMP '2022-05-05' AS time_create, TIMESTAMP '2022-05-05' AS time_update, TIMESTAMP '2022-05-06' AS time_delete)], TIMESTAMP '2022-05-07')[order_id] -- Empty when the reference time is after time_delete
;