tsid

Downloads

766

GitHub stars

Extension repository on GitHub

DuckDB Time-Sortable ID generator

Maintainer(s): lmangani

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 │
└──────────────────────────────────┴──────────────────────────────────┴────────────────────────────┴────────────────────────────┘

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.