Using Time-Based Values as Primary Keys in SQLite: Considerations and Solutions

Temporal Primary Key Implementation Challenges in Database Design

The use of time-based values as primary keys in SQLite involves balancing four critical factors: uniqueness guarantees, timestamp precision granularity, storage efficiency tradeoffs, and calendrical complexity from leap seconds. This guide dissects the technical constraints and optimization strategies when implementing temporal primary keys through real-world schema examples and SQLite internals analysis.

Core Constraints of Temporal Primary Key Architectures

Uniqueness Enforcement in High-Frequency Insertion Scenarios

Time-based primary keys rely on the assumption that timestamp generation occurs at a frequency higher than the system’s minimum time resolution. SQLite’s default INTEGER PRIMARY KEY autoincrement behavior guarantees uniqueness through monotonic incrementing integers managed at the transaction level. In contrast, external timestamp generators like smalltime() or nanotime() shift uniqueness enforcement to the application layer.

Microsecond-level timestamps (e.g., 48-bit smalltime) theoretically provide 1,000,000 unique values per second. However, practical uniqueness depends on:

1. System Clock Granularity: Windows APIs typically offer 15ms resolution, Linux CLOCK_REALTIME ~1μs, but actual precision varies by hardware
2. Insertion Concurrency: Parallel transactions generating timestamps via strftime('%f') may collide
3. Time Source Synchronization: NTP adjustments and clock drift compensation algorithms can create timestamp regressions

The forum example demonstrates a 2Hz sensor system where application-layer controls prevent collisions by spacing inserts 500ms apart. This works when ingestion rates and timing are fully controlled by a single process. Distributed systems or multi-threaded inserters require additional coordination mechanisms like:

• Sequence Number Suffixes: Appending 2-4 bits to store collision counters (0101-1, 0101-2)
• Hybrid Keys: Combining timestamp with worker/thread identifiers (0101-W1, 0101-W2)
• Pessimistic Locking: Global mutexes around timestamp generation

Storage Efficiency vs. Temporal Precision Tradeoffs

SQLite’s rowid storage uses variable-length integers (1-9 bytes) for primary keys. Custom temporal keys in WITHOUT ROWID tables store each key component as separate columns with fixed-size integer types. Consider two approaches:

Approach A (rowid-based):

CREATE TABLE events(
   event_time INTEGER PRIMARY KEY,  -- 64-bit nanotime
   data BLOB
);

• Pros: Automatic 1-9 byte storage via varint encoding
• Cons: Time decomposition requires bitmask operations

Approach B (composite key):

CREATE TABLE events(
   days INTEGER,   -- Days since epoch (16-bit)
   usec INTEGER,   -- Microseconds in day (32-bit)
   data BLOB,
   PRIMARY KEY(days, usec)
) WITHOUT ROWID;

• Pros: Direct date extraction via days column
• Cons: Fixed 6-byte key + 1-byte header per column

Storage analysis for 1 billion rows:

• Approach A: ~5.5GB (avg 5.5 bytes/rowid)
• Approach B: ~7GB (6-byte key + 2×1-byte headers)

The forum’s day2000+msec schema uses 6 bytes (2+4) but gains human-readable date splits without computed columns. Storage penalties emerge from:

• Header Overheads: Each column adds 1-byte type/size info
• Alignment Padding: 32/64-bit alignment can pad rows to word boundaries
• Index Bloat: Secondary indexes duplicate key columns

Leap Second Handling in Temporal Representations

SQLite’s date/time functions assume UTC-SLS (smear leap seconds) rather than tracking actual SI seconds. A timestamp in the 23:59:60 leap second window converts to 23:59:59.999 in SQLite. The forum schema avoids this by storing raw milliseconds since 2000-01-01, enabling leap second preservation through:

1. Epoch Anchoring: Base date before leap second accumulations (2000 vs 1970)
2. Separate Date Components: Day counter isolates leap second handling to msec field
3. Raw Value Storage: Avoids SQLite’s internal leap second smearing

This allows:

-- Insert leap second event
INSERT INTO timeseries(day2000, msec, ...)
VALUES (7305, 86400000); -- 86400000ms = 24:00:00 (leap second)

-- Query leap second data
SELECT * FROM timeseries 
WHERE day2000=7305 AND msec BETWEEN 86399000 AND 86400000;

However, application code must handle:

• UTC-TAI Offsets: Maintain table of historical leap seconds
• Time Zone Adjustments: Convert local time with DST to UTC before storage
• Calendar Arithmetic: Add 86400000 msec = 1 day in non-leap days

Temporal Key Collision Mitigation Strategies

Hardware-Assisted Timestamp Generation

Embedded systems can leverage hardware counters for collision-free timestamps:

// STM32 HAL example
uint64_t get_chip_time() {
    return DWT->CYCCNT; // Cycle counter @ CPU frequency
}

-- SQLite C extension
static void smalltimeFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  uint64_t t = get_chip_time();
  sqlite3_result_int64(context, t);
}

Register function:

sqlite3_create_function(db, "smalltime", 0, SQLITE_UTF8, 0, smalltimeFunc, 0, 0);

This provides monotonic timestamps immune to system clock adjustments. Storage considerations:

• Rollover Handling: 64-bit counters at 1GHz take 585 years to roll over
• Frequency Calibration: Map counter ticks to SI seconds via RTC or NTP

Statistical Collision Probability Analysis

For systems without guaranteed insertion rates, calculate collision risk:

Given:
- Timestamp resolution: R (e.g., 1μs = 1e6/s)
- Insertion rate: N inserts/second
- Collision probability: P

P ≈ N²/(2R) (Birthday problem approximation)

Example:
N=10,000 inserts/s
R=1,000,000/s (μs)
P ≈ 10⁸/(2e6) = 5% per second

To achieve P<0.1%:

R ≥ N²/(2*0.001) → R ≥ 500N²
For N=1000 → R ≥ 500 million/s → 2ns resolution

Thus, microsecond resolution becomes insufficient beyond ~450 inserts/s.

Hybrid Key Design Patterns

Combine temporal components with entropy sources:

Pattern 1: Timestamp + Sequence

CREATE TABLE events(
   ts INTEGER,  -- Microseconds since epoch
   seq INTEGER, -- Per-ts sequence (0-15)
   data BLOB,
   PRIMARY KEY(ts, seq)
) WITHOUT ROWID;

Allocate 4 bits (16 values) for sequence, reducing required timestamp resolution 16x.

Pattern 2: Sharded Timestamp

CREATE TABLE events(
   ts INTEGER,     -- Second precision
   node_id INTEGER,-- Shard ID (0-65535)
   data BLOB,
   PRIMARY KEY(ts, node_id)
) WITHOUT ROWID;

16-bit shard ID allows 65k inserts/second at 1Hz timestamp resolution.

Optimized Temporal Schema Implementation Guide

Step 1: Temporal Resolution Requirements Audit

1. Minimum Time Unit: Determine required precision (ms, μs, ns)
2. Maximum Insert Rate: Peak writes/second across all threads/processes
3. Clock Sources: Assess NTP sync stability, hardware timestamp availability

Example spec:

• Precision: 100μs (10,000/s)
• Max inserts: 500/s
• Clock: Linux CLOCK_REALTIME (1μs resolution)

Collision probability: 500²/(2*10,000) = 12.5% per second → Unacceptable.

Step 2: Schema Prototyping with Worst-Case Sizing

Model storage for maximum anticipated data:

-- Option A: rowid temporal
CREATE TABLE telemetry(
   ts INTEGER PRIMARY KEY, -- 100μs since 2024
   val REAL
);

-- Storage for 10 years @ 500/s:
10y * 31536000s/y * 500/s = 157.7B rows
rowid size: log₂(157.7B) ≈ 38 bits → 5 bytes avg
Total: 157.7B * (5 + 8(val) + 1(header)) = 2.2TB

-- Option B: composite WITHOUT ROWID
CREATE TABLE telemetry(
   day INTEGER, -- Days since 2024 (2 bytes)
   usec INTEGER,-- 100μs in day (4 bytes)
   val REAL,
   PRIMARY KEY(day, usec)
) WITHOUT ROWID;

Per row: 2+4+8 + 3 headers = 17 bytes
Total: 157.7B * 17 = 2680GB (+55% vs Option A)

Storage optimization strategies:

• Column Compression: Store val as 4-byte float if precision permits
• Dictionary Encoding: Map repetitive val to lookup tables
• Column Ordering: Place fixed-size columns first to minimize padding

Step 3: Collision Handling Implementation

Implement UPSERT with fallback sequencing:

CREATE TABLE events(
   ts INTEGER PRIMARY KEY,
   seq INTEGER DEFAULT 0,
   data TEXT
);

-- Insert with retry loop (pseudocode)
void insertWithRetry(sqlite3* db, Data data) {
    int retries = 0;
    while(retries++ < 5) {
        int64_t ts = getTimestamp();
        sqlite3_exec("BEGIN IMMEDIATE");
        // Check for existing ts
        int64_t count = sqlite3_query("SELECT COUNT(*) FROM events WHERE ts=?", ts);
        if(count == 0) {
            sqlite3_exec("INSERT INTO events(ts,data) VALUES(?,?)", ts, data);
            sqlite3_exec("COMMIT");
            return;
        } else {
            // Increment sequence on collision
            int64_t max_seq = sqlite3_query("SELECT MAX(seq) FROM events WHERE ts=?", ts);
            sqlite3_exec("INSERT INTO events(ts,seq,data) VALUES(?,?,?)", 
                         ts, max_seq+1, data);
            sqlite3_exec("COMMIT");
            return;
        }
    }
    // Handle failure after retries
}

Index optimization:

CREATE INDEX events_ts_seq ON events(ts, seq);

Step 4: Leap Second and Time Zone Testing

Validate temporal logic with edge cases:

-- Leap second insertion test
INSERT INTO timeseries(day2000, msec) 
VALUES (9461, 86400000); -- 2000-01-01 + 9461 days = 2025-12-31 + leap second

-- Query leap second day
SELECT * FROM timeseries 
WHERE day2000=9461 AND msec BETWEEN 86300000 AND 86400000;

-- Time zone boundary test
INSERT INTO timeseries(day2000, msec)
VALUES (1234, 0); -- 2000-01-01 +1234 days in UTC
-- Convert to America/New_York with DST
SELECT dttmstr, datetime(dttmstr, 'localtime') FROM timeseries WHERE day2000=1234;

Test coverage should include:

• Daylight Saving Time transitions
• 24:00:00 vs 00:00:00 next day handling
• strftime %j (day of year) across leap years
• BETWEEN queries spanning day boundaries

Step 5: Query Performance Optimization

Temporal range queries benefit from clustered indexes:

-- Original schema
EXPLAIN QUERY PLAN
SELECT * FROM timeseries 
WHERE day2000 BETWEEN 9000 AND 9001 
  AND msec BETWEEN 43200000 AND 64800000;

-- Outputs:
SEARCH TABLE timeseries USING PRIMARY KEY (day2000>? AND day2000<? AND msec>? AND msec<?)

Optimize via covering indexes:

CREATE INDEX ts_covering ON timeseries(day2000, msec) INCLUDE (fac, fac_error);

For partitioned tables:

-- Daily partitioning
ATTACH 'timeseries_2024.db' AS ts2024;
CREATE TABLE ts2024.timeseries (...) WITHOUT ROWID;
-- Query across partitions
SELECT * FROM ts2024.timeseries WHERE day2000=... 
UNION ALL 
SELECT * FROM ts2025.timeseries WHERE day2000=...;

Benchmark with different page sizes:

sqlite3 db.sqlite "PRAGMA page_size=16384; VACUUM;"

Larger pages (16KB vs default 4KB) improve range scan throughput for temporal data.

Conclusion

Implementing time-based primary keys in SQLite requires meticulous analysis of insertion patterns, storage tradeoffs, and temporal edge cases. The optimal design balances the application’s collision tolerance against the complexity of composite keys and leap second handling. For high-frequency ingestion systems, hybrid keys with hardware-assisted timestamps and collision buffers provide robust uniqueness guarantees. Analytical time-series workloads benefit from partitioned WITHOUT ROWID tables with covering indexes, while general-purpose applications may prefer INTEGER PRIMARY KEY simplicity. Rigorous testing across time zone transitions and leap second boundaries ensures temporal query correctness regardless of the chosen schema strategy.