ULID functions

ULID (Universally Unique Lexicographically Sortable Identifier) functions provide a way to generate and manipulate unique, sortable identifiers.

ULIDs are 128-bit identifiers that combine a timestamp with random data, offering advantages over traditional UUIDs such as:

1. Lexicographic sorting: ULIDs can be sorted by their string representation, which corresponds to the order of their creation.
2. Timestamp encoding: The first 48 bits of a ULID contain a millisecond-precision timestamp, allowing for easy extraction of creation time.
3. Monotonicity: ULIDs generated in the same millisecond are guaranteed to be sortable and unique.

​

ClickHouse function reference

​

generateULID

Generates a ULID (Universally Unique Lexicographically Sortable Identifier).

Syntax:

generateULID([x])

Arguments:

• x (optional): An expression of any supported data type. The value is discarded, but the expression is used to bypass common subexpression elimination when the function is called multiple times in one query.

Returns:

A FixedString(26) value representing the generated ULID.

Example:

Generate a single ULID:

SELECT
	generateULID() AS taco_order_id

Result:

| taco_order_id                |
|------------------------------|
| 01H1VXJF85N7Q3KQCSPBW10MGN   |

Generate multiple ULIDs in one row:

SELECT
	generateULID(1) AS beef_taco_id,
	generateULID(2) AS chicken_taco_id

Result:

| beef_taco_id                | chicken_taco_id             |
|-----------------------------|---------------------------- |
| 01H1VXJF85TACO3KQBEEF10MG   | 01H1VXJF85TACO3KQCHKN10MG   |

In this example, we generate unique IDs for different types of tacos in a single query. The arguments 1 and 2 ensure that each generateULID() call produces a distinct value.

​

ULIDStringToDateTime

Extracts the timestamp from a ULID (Universally Unique Lexicographically Sortable Identifier).

Syntax:

ULIDStringToDateTime(ulid[, timezone])

Arguments:

• ulid (String or FixedString(26)): The input ULID.
• timezone (String, optional): The timezone name for the returned value.

Returns:

A timestamp with millisecond precision. [DateTime64(3)]

Example:

SELECT
	ULIDStringToDateTime('01H1VECCJCP3QXSBTQ1XJZE8J4') AS taco_order_time,
	generateULID() AS new_order_id
LIMIT 1;

Result:

| taco_order_time         | new_order_id               |
|-------------------------|----------------------------|
| 2023-06-01 12:19:37.676 | 01H1VECCJCP3QXSBTQ1XJZE8J4 |

In this example:

• taco_order_time extracts the timestamp from a ULID representing a taco order.
• new_order_id generates a new ULID for the next order.