Temporal Tables & Time-Travel Queries

-- Temporal tables track the HISTORY of every row
-- "What did this data look like last Tuesday at 3 PM?"

-- Two types of temporal data:
-- 1. SYSTEM-TIME (transaction time): auto-managed by DB
--    Records when the DB stored the data
-- 2. APPLICATION-TIME (valid time): managed by your app
--    Records when the data is valid in the real world

-- Example: Employee salary history
-- Without temporal: UPDATE overwrites the old salary — gone forever
-- With temporal: every change is preserved a
...

-- PostgreSQL: Manual temporal table implementation
-- (PostgreSQL doesn't have built-in temporal tables yet)

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50),
    -- Temporal columns
    valid_from TIMESTAMP NOT NULL DEFAULT NOW(),
    valid_to TIMESTAMP NOT NULL DEFAULT 'infinity',
    -- Track who made the change
    modified_by VARCHAR(100) DEFAULT current_user
);

-- History table (stores old versi
...

-- TIME-TRAVEL QUERIES: query data as it was at any point in time

-- SQL Server syntax (built-in):
-- SELECT * FROM employees
-- FOR SYSTEM_TIME AS OF '2024-06-15 14:30:00';

-- PostgreSQL manual approach:
-- "What was the product price on June 15, 2024?"
SELECT * FROM (
    -- Current rows valid at that time
    SELECT * FROM products
    WHERE valid_from <= '2024-06-15 14:30:00'
      AND valid_to > '2024-06-15 14:30:00'
    UNION ALL
    -- Historical rows valid at that time
    SELECT * FRO
...

-- APPLICATION-TIME: track when data is valid in reality
-- Example: Insurance policies, subscriptions, room bookings

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    plan VARCHAR(50) NOT NULL,
    monthly_price DECIMAL(8,2),
    -- Application-time period
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    CHECK (valid_from < valid_to),
    -- Prevent overlapping subscriptions per customer
    EXCLUDE USING gist (
        customer_id WITH =,
 
...

-- Temporal tables for compliance and auditing

-- "Show me every change to this employee's record"
SELECT
    h.name,
    h.salary,
    h.department,
    h.valid_from AS changed_at,
    h.valid_to AS superseded_at,
    h.modified_by
FROM products_history h
WHERE h.id = 42
ORDER BY h.valid_from;

-- "What changed between two dates?"
SELECT
    COALESCE(old.id, new.id) AS product_id,
    old.price AS old_price,
    new.price AS new_price,
    CASE
        WHEN old.id IS NULL THEN 'ADDED'
        
...