---
title: "AS OF Quick Reference Guide"
---

# AS OF Quick Reference Guide

## Usage Examples

### Basic Time-Travel Queries

```rust
use heliosdb_nano::sql::{LogicalPlan, AsOfClause};
use std::sync::Arc;

// AS OF TIMESTAMP
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Timestamp("2025-11-19 12:00:00".to_string())),
};

// AS OF TRANSACTION
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Transaction(12345)),
};

// AS OF SCN
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(67890)),
};

// AS OF NOW
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Now),
};

// Execute
let mut executor = Executor::with_storage(&engine);
let results = executor.execute(&plan)?;
```

### SQL Syntax (Future Parser Integration)

```sql
-- Timestamp-based
SELECT * FROM orders AS OF TIMESTAMP '2025-11-19 12:00:00';

-- Transaction-based
SELECT * FROM orders AS OF TRANSACTION 12345;

-- SCN-based
SELECT * FROM orders AS OF SCN 67890;

-- Current time
SELECT * FROM orders AS OF NOW;
```

## AsOfClause Definition

```rust
pub enum AsOfClause {
    /// AS OF NOW - current time
    Now,
    /// AS OF TIMESTAMP '2025-11-15 06:00:00'
    Timestamp(String),
    /// AS OF TRANSACTION 987654
    Transaction(u64),
    /// AS OF SCN 123456789 (System Change Number)
    Scn(u64),
}
```

## Resolution Flow

```
AsOfClause
    ↓
SnapshotManager::resolve_as_of()
    ↓
    ├─ Timestamp → resolve_timestamp() → u64 (snapshot_ts)
    ├─ Transaction → resolve_transaction() → u64 (snapshot_ts)
    └─ Scn → resolve_scn() → u64 (snapshot_ts)
    ↓
StorageEngine::scan_table_at_snapshot(table_name, snapshot_ts)
    ↓
Vec<Tuple> (historical data)
```

## Error Handling

```rust
// Invalid timestamp format
AsOfClause::Timestamp("invalid")
// → Error: "Invalid timestamp format: ..."

// Non-existent transaction
AsOfClause::Transaction(99999)
// → Error: "Transaction 99999 not found or has been garbage collected"

// Non-existent SCN
AsOfClause::Scn(99999)
// → Error: "SCN 99999 not found or has been garbage collected"

// No snapshot at timestamp
AsOfClause::Timestamp("1970-01-01 00:00:00")
// → Error: "No snapshot found for timestamp '1970-01-01 00:00:00'"
```

## Timestamp Formats Supported

```rust
// PostgreSQL format
"2025-11-19 12:00:00"

// ISO 8601 format
"2025-11-19T12:00:00"
```

## Complex Query Examples

### With Filter

```rust
let scan_plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Transaction(100)),
};

let filter_plan = LogicalPlan::Filter {
    input: Box::new(scan_plan),
    predicate: LogicalExpr::BinaryExpr {
        left: Box::new(LogicalExpr::Column { name: "customer".to_string() }),
        op: BinaryOperator::Eq,
        right: Box::new(LogicalExpr::Literal(Value::String("Alice".to_string()))),
    },
};
```

### With Join

```rust
let orders_scan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(orders_schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(5000)),
};

let customers_scan = LogicalPlan::Scan {
    table_name: "customers".to_string(),
    schema: Arc::new(customers_schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(5000)), // Same snapshot for consistency
};

let join_plan = LogicalPlan::Join {
    left: Box::new(orders_scan),
    right: Box::new(customers_scan),
    join_type: JoinType::Inner,
    on: Some(join_condition),
};
```

## Getting Snapshot Information

```rust
// Get snapshot manager
let snapshot_mgr = engine.snapshot_manager();

// Get snapshot by timestamp
let metadata = snapshot_mgr.get_snapshot_metadata(timestamp_value)?;
println!("Transaction ID: {}", metadata.transaction_id);
println!("SCN: {}", metadata.scn);
println!("Wall clock time: {}", metadata.wall_clock_time);

// Get current SCN
let current_scn = snapshot_mgr.current_scn();

// Get current transaction ID
let current_txn = snapshot_mgr.current_transaction_id();

// Get snapshot count
let count = snapshot_mgr.snapshot_count();
```

## Creating Versioned Data

```rust
// Insert with automatic versioning
let row_id = engine.insert_tuple_versioned("orders", tuple)?;
// This automatically:
// 1. Creates a new snapshot
// 2. Assigns transaction ID and SCN
// 3. Stores versioned copy for time-travel
```

## Performance Tips

1. **Use SCN or Transaction ID when possible** - Faster than timestamp parsing
2. **Coordinate AS OF across tables in JOINs** - Ensures consistent snapshot view
3. **Normal queries have zero overhead** - Don't use AS OF if not needed
4. **Monitor snapshot count** - Large retention can increase memory usage
5. **Use AS OF NOW for explicit current-time semantics**

## Debugging

Enable tracing to see AS OF resolution:

```rust
tracing::subscriber::set_global_default(
    tracing_subscriber::fmt()
        .with_max_level(tracing::Level::DEBUG)
        .finish()
).unwrap();
```

Output:
```
DEBUG Time-travel query on table 'orders' with AS OF clause: Transaction(12345)
DEBUG Resolved AS OF clause to snapshot timestamp 100 for table 'orders'
DEBUG Time-travel scan returned 42 tuples from table 'orders' at snapshot 100
```

## Testing

```bash
# Run all AS OF tests
cargo test test_executor_as_of -- --nocapture

# Run specific AS OF test
cargo test test_executor_as_of_timestamp -- --nocapture

# Run integration tests
cargo test --test time_travel_integration_tests -- --nocapture
```

## Common Pitfalls

1. **Different snapshots in JOIN** - Use same AS OF for all tables
2. **Garbage collected snapshots** - Old snapshots may be removed
3. **Timestamp precision** - Only second-level granularity
4. **Memory usage** - Large retention policies increase memory

## API Reference

### Executor Methods

```rust
impl Executor {
    // Execute plan with AS OF support
    pub fn execute(&mut self, plan: &LogicalPlan) -> Result<Vec<Tuple>>;
}
```

### SnapshotManager Methods

```rust
impl SnapshotManager {
    // Resolve AS OF clause to snapshot timestamp
    pub fn resolve_as_of(&self, as_of: &AsOfClause) -> Result<u64>;

    // Get snapshot metadata
    pub fn get_snapshot_metadata(&self, timestamp: u64) -> Option<SnapshotMetadata>;

    // Get current SCN
    pub fn current_scn(&self) -> Scn;

    // Get current transaction ID
    pub fn current_transaction_id(&self) -> TransactionId;

    // Get snapshot count
    pub fn snapshot_count(&self) -> usize;
}
```

### StorageEngine Methods

```rust
impl StorageEngine {
    // Get snapshot manager
    pub fn snapshot_manager(&self) -> &SnapshotManager;

    // Scan table at historical snapshot
    pub fn scan_table_at_snapshot(&self, table_name: &str, snapshot_ts: u64) -> Result<Vec<Tuple>>;

    // Insert versioned tuple
    pub fn insert_tuple_versioned(&self, table_name: &str, tuple: Tuple) -> Result<u64>;
}
```

---

**Quick Links**:
- Full Implementation: AS_OF_EXECUTOR_IMPLEMENTATION.md
- Time-Travel Guide: [TIME_TRAVEL_QUICK_REFERENCE.md](https://www.heliosdb.com/docs/nano/guides/quickstart/time_travel_quick_reference/)
- Architecture: docs/architecture/TIME_TRAVEL_ARCHITECTURE.md