DuckDB Time-Sortable ID generator
Maintainer(s):
lmangani,
rustyconover
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL tsid FROM community;
LOAD tsid;
Example
-- Generate a new time-sortable ID (accepts an optional seed string)
D SELECT tsid();
┌──────────────────────────────────┐
│ tsid() │
│ varchar │
├──────────────────────────────────┤
│ 675716e86985495e9cf575f0b9c4a8db │
└──────────────────────────────────┘
-- Extract timestamp from a TSID
D SELECT tsid_to_timestamp('675716e86985495e9cf575f0b9c4a8db');
┌───────────────────────────────────────────────────────┐
│ tsid_to_timestamp('675716e86985495e9cf575f0b9c4a8db') │
│ timestamp │
├───────────────────────────────────────────────────────┤
│ 2024-12-09 16:12:24.44259 │
└───────────────────────────────────────────────────────┘
-- Verify TSID ordering
D SELECT
a.id as id1,
b.id as id2,
tsid_to_timestamp(a.id) as ts1,
tsid_to_timestamp(b.id) as ts2
FROM
(SELECT tsid('a') as id FROM range(1)) a,
(SELECT tsid('b') as id FROM range(1)) b;
┌──────────────────────────────────┬──────────────────────────────────┬────────────────────────────┬────────────────────────────┐
│ id1 │ id2 │ ts1 │ ts2 │
│ varchar │ varchar │ timestamp │ timestamp │
├──────────────────────────────────┼──────────────────────────────────┼────────────────────────────┼────────────────────────────┤
│ 6757176b9d4640b299be06ff79ed1373 │ 6757176b9d704dcf8a1dce8bb6da51bf │ 2024-12-09 16:14:35.659653 │ 2024-12-09 16:14:35.660354 │
└──────────────────────────────────┴──────────────────────────────────┴────────────────────────────┴────────────────────────────┘
About tsid
For more information regarding usage, see the documentation.
Added Functions
This extension does not add any functions.
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.