Time Travel

-- Check what data existed before deletion
SELECT * FROM users FOR SYSTEM_TIME AS OF '-2h' 
WHERE department = 'Engineering';
 
-- Restore the deleted records
INSERT INTO users 
SELECT * FROM users FOR SYSTEM_TIME AS OF '-2h'
WHERE id IN (101, 102, 103);

-- Compare current state with past state
SELECT 
    current.id,
    current.status AS current_status,
    past.status AS past_status,
    current.updated_at
FROM orders current
JOIN orders FOR SYSTEM_TIME AS OF '-1h' past 
    ON current.id = past.id
WHERE current.status != past.status;

-- Compare user counts at different time points
SELECT 
    'current' AS timepoint,
    COUNT(*) AS user_count
FROM users
UNION ALL
SELECT 
    '1 hour ago',
    COUNT(*)
FROM users FOR SYSTEM_TIME AS OF '-1h'
UNION ALL  
SELECT 
    '1 day ago',
    COUNT(*)
FROM users FOR SYSTEM_TIME AS OF '-24h';

-- Start transaction at a specific point in time
BEGIN FOR SYSTEM_TIME AS OF '-30s';
    -- All queries in this transaction see data from 30 seconds ago
    SELECT COUNT(*) FROM orders WHERE status = 'pending';
    SELECT SUM(amount) FROM payments WHERE created_at > '2024-01-01';
COMMIT;

-- Simple relative times
SELECT COUNT(*) FROM products FOR SYSTEM_TIME AS OF '-10s';   -- 10 seconds ago
SELECT * FROM users FOR SYSTEM_TIME AS OF '-5m';             -- 5 minutes ago  
SELECT * FROM orders FOR SYSTEM_TIME AS OF '-2h';            -- 2 hours ago
SELECT * FROM logs FOR SYSTEM_TIME AS OF '-1d';              -- 1 day ago

-- Get nanosecond timestamp for specific datetime
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-09-30 15:15:00') * 1000000000;
-- Result: 1727680500000000000
 
-- Query using exact timestamp
SELECT * FROM transactions 
FOR SYSTEM_TIME AS OF '1727680500000000000';

-- Compare data from different time points
SELECT 
    u.username,
    o1.order_count AS current_orders,
    o2.order_count AS yesterday_orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY user_id
) o1 ON u.id = o1.user_id
JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders FOR SYSTEM_TIME AS OF '-24h'
    GROUP BY user_id  
) o2 ON u.id = o2.user_id;

-- Set time for entire transaction
BEGIN FOR SYSTEM_TIME AS OF '-1h';
    -- All queries use data from 1 hour ago
    UPDATE inventory SET quantity = quantity + 100 
    WHERE product_id = 'PROD001';
    
    INSERT INTO audit_log (action, timestamp) 
    VALUES ('inventory_adjustment', NOW());
COMMIT;

-- Create materialized view
CREATE MATERIALIZED VIEW daily_sales AS 
SELECT DATE(created_at) AS sale_date, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE(created_at);
 
-- Query historical state of materialized view
SELECT * FROM daily_sales FOR SYSTEM_TIME AS OF '-6h'
WHERE sale_date = '2024-09-30';

-- Find all changes to user permissions in the last hour
WITH current_perms AS (
    SELECT user_id, permissions 
    FROM user_permissions
),
past_perms AS (
    SELECT user_id, permissions 
    FROM user_permissions FOR SYSTEM_TIME AS OF '-1h'
)
SELECT 
    c.user_id,
    p.permissions AS old_permissions,
    c.permissions AS new_permissions
FROM current_perms c
FULL OUTER JOIN past_perms p ON c.user_id = p.user_id
WHERE c.permissions != p.permissions 
   OR c.user_id IS NULL 
   OR p.user_id IS NULL;

-- Set during table creation (6000 seconds = 100 minutes)
CREATE TABLE user_activity (
    user_id INT,
    action TEXT,
    timestamp TIMESTAMPTZ DEFAULT NOW()
) WITH (gc_ttl_seconds = 6000);
 
-- Modify existing table (23 hours retention)
ALTER TABLE user_activity SET (gc_ttl_seconds = 82800);
 
-- Check current setting
SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'user_activity';