---
title: "Time-Travel Queries"
---

# Time-Travel Queries

Query your data as it existed at any point in the past using HeliosDB Nano's time-travel feature.

## Overview

Time-travel queries allow you to:

- **Audit changes**: See what data looked like before modifications
- **Debug issues**: Understand how data changed over time
- **Recover data**: Find and restore accidentally deleted records
- **Compare states**: Diff current vs historical data

## Syntax

### Query by Timestamp

```sql
SELECT * FROM orders
AS OF TIMESTAMP '2025-01-15 10:30:00';
```

### Query by Transaction ID

```sql
SELECT * FROM orders
AS OF TRANSACTION 12345;
```

### Query by System Change Number (SCN)

```sql
SELECT * FROM orders
AS OF SCN 50000;
```

## Examples

### View Historical Data

```sql
-- See orders from yesterday
SELECT * FROM orders
AS OF TIMESTAMP '2025-01-14 00:00:00'
WHERE status = 'completed';
```

### Compare Current vs Historical

```sql
-- Find price changes in the last week
SELECT
    c.id,
    c.price as current_price,
    h.price as week_ago_price,
    c.price - h.price as change
FROM products c
JOIN products AS OF TIMESTAMP '2025-01-08 00:00:00' h
    ON c.id = h.id
WHERE c.price != h.price;
```

### Find Deleted Records

```sql
-- Find records that existed yesterday but don't exist now
SELECT h.*
FROM orders AS OF TIMESTAMP '2025-01-14 00:00:00' h
LEFT JOIN orders c ON h.id = c.id
WHERE c.id IS NULL;
```

### Audit Trail

```sql
-- See the state of a specific order at different times
SELECT 'Current' as version, * FROM orders WHERE id = 123
UNION ALL
SELECT '1 hour ago', * FROM orders AS OF TIMESTAMP '2025-01-15 09:00:00' WHERE id = 123
UNION ALL
SELECT 'Yesterday', * FROM orders AS OF TIMESTAMP '2025-01-14 10:00:00' WHERE id = 123;
```

## REPL Commands

```bash
# Show current LSN (Log Sequence Number)
\show lsn

# Show available snapshots
\snapshots
```

## Performance

Time-travel queries have approximately **<2x overhead** compared to current-data queries due to:

1. Snapshot lookup
2. Version filtering
3. Page reconstruction (if needed)

### Optimization Tips

- Use specific timestamps rather than ranges
- Add indexes on commonly queried historical columns
- Configure snapshot retention for your use case

## Snapshot Management

### View Snapshots

```sql
SELECT * FROM pg_snapshots();
```

### Retention

Snapshots are retained based on configuration:

```toml
[storage]
snapshot_retention_days = 30
snapshot_gc_interval = 3600  # seconds
```

Older snapshots are automatically garbage collected.

## Limitations

- Cannot query data older than the oldest retained snapshot
- JOINs between current and historical data may be slower
- Some DDL changes may affect historical query availability

## Use Cases

### Compliance & Auditing

```sql
-- Prove what data existed at report time
SELECT * FROM financial_records
AS OF TIMESTAMP '2024-12-31 23:59:59'
WHERE account_id = 'ACCT001';
```

### Data Recovery

```sql
-- Recover accidentally deleted customer
INSERT INTO customers
SELECT * FROM customers
AS OF TIMESTAMP '2025-01-15 09:00:00'
WHERE id = 456;
```

### A/B Testing Analysis

```sql
-- Compare metrics before and after feature launch
WITH before AS (
    SELECT COUNT(*) as orders_before
    FROM orders AS OF TIMESTAMP '2025-01-01 00:00:00'
),
after AS (
    SELECT COUNT(*) as orders_after FROM orders
)
SELECT * FROM before, after;
```

## Related

- [Database Branching](https://www.heliosdb.com/docs/nano/features/branching/) - Create isolated copies for testing
- [Materialized Views](https://www.heliosdb.com/docs/nano/features/materialized-views/) - Pre-compute historical aggregates