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
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| tsid | scalar | Generates a new Time-Sorted Unique Identifier (TSID). TSIDs are chronologically sortable 128-bit unique identifiers that embed a timestamp, making them ideal for distributed systems and time-series data. | NULL | [tsid()] |
| tsid_to_timestamp | scalar | Extracts the embedded timestamp from a TSID. Returns the timestamp that was recorded when the TSID was generated. | NULL | [tsid_to_timestamp('0193b9c8d23d7192bc1cc82b43e6e8f3')] |
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.