Functions for Working with ULID

These functions allow you to generate and extract information from Universally Unique Lexicographically Sortable Identifiers (ULIDs).

generateULID

Generates a new Universally Unique Lexicographically Sortable Identifier (ULID). ULIDs are designed to be sortable by time and are 26 characters long.

Syntax

generateULID([x])

Arguments

  • x: Any data type. This optional argument is used to ensure a new ULID is generated if the function is called multiple times within a single query, preventing common subexpression elimination.

Returns

A FixedString(26) value representing the newly generated ULID.

Example

SELECT generateULID()

Result:

┌─generateULID()─────────────┐
│ 01H00000000000000000000000 │
└────────────────────────────┘

ULIDStringToDateTime

Extracts the timestamp component embedded within a ULID string.

Syntax

ULIDStringToDateTime(ulid[, timezone])

Arguments

  • ulid: String or FixedString(26). The ULID string from which to extract the timestamp.
  • timezone: String. An optional timezone name to apply to the returned DateTime64 value.

Returns

A DateTime64(3) value representing the timestamp from the ULID, with millisecond precision.

Example

SELECT ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')

Result:

┌─ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')─┐
│                            2022-12-28 00:40:37.616 │
└────────────────────────────────────────────────────┘
Updated