Storing High-Resolution Timestamps in SQLite #10

New Issue

2026-10-05T06:01:49+02:00

adam commented

2026-10-05 06:01:49 +02:00

Storing High-Resolution Timestamps in SQLite

Right now, timestamps like:

2025-03-12 04:15:56.058038

are stored as dateime TEXT in ISO8601 format with microseconds. While this is human-readable and sorts correctly as strings, it’s inefficient for math, filtering, and indexing.

Recommendation

Keep a parallel INTEGER column that stores microseconds since the Unix epoch (1970-01-01 00:00:00 UTC). This gives you:

Compact storage
Fast comparisons and math (BETWEEN, ranges, etc.)
Index support for date filters
Microsecond resolution

Migration Steps

Add the new column

   ALTER TABLE packet ADD COLUMN import_time_us INTEGER;

** populate it **

UPDATE packet
SET import_time_us =
    CAST((strftime('%s', import_time) || substr(import_time, 21, 6)) AS INTEGER);

Explanation:
• strftime('%s', import_time) → seconds since epoch
• substr(import_time, 21, 6) → extracts the microsecond part
• Concatenate and cast to integer → microsecond epoch

Index it.

CREATE INDEX idx_packet_import_time_us ON packet(import_time_us);

then we can use queries clauses like this

WHERE import_time_us BETWEEN strftime('%s','now','-7 days') * 1000000
                        AND strftime('%s','now') * 1000000

ideally we can covert all our calls to use these new columns.

## Storing High-Resolution Timestamps in SQLite Right now, timestamps like: `2025-03-12 04:15:56.058038` are stored as dateime **TEXT** in ISO8601 format with microseconds. While this is human-readable and sorts correctly as strings, it’s inefficient for math, filtering, and indexing. ### Recommendation Keep a **parallel INTEGER column** that stores microseconds since the Unix epoch (1970-01-01 00:00:00 UTC). This gives you: - Compact storage - Fast comparisons and math (`BETWEEN`, ranges, etc.) - Index support for date filters - Microsecond resolution ### Migration Steps 1. **Add the new column** ```sql ALTER TABLE packet ADD COLUMN import_time_us INTEGER; ``` 2. ** populate it ** ```sql UPDATE packet SET import_time_us = CAST((strftime('%s', import_time) || substr(import_time, 21, 6)) AS INTEGER); ``` Explanation: • strftime('%s', import_time) → seconds since epoch • substr(import_time, 21, 6) → extracts the microsecond part • Concatenate and cast to integer → microsecond epoch 3. Index it. ```sql CREATE INDEX idx_packet_import_time_us ON packet(import_time_us); ``` 4. then we can use queries clauses like this ```sql WHERE import_time_us BETWEEN strftime('%s','now','-7 days') * 1000000 AND strftime('%s','now') * 1000000 ``` ideally we can covert all our calls to use these new columns.

adam closed this issue

2026-10-05 06:01:49 +02:00

adam commented

2026-10-05 06:01:50 +02:00

This feature has been added to version 3

This feature has been added to version 3

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: adam/meshview#10