Functions for Working with ULID

generateULID

Generates the ULID.

Syntax

generateULID([x])

Arguments

• x — Expression resulting in any of the supported data types. The resulting value is discarded, but the expression itself if used for bypassing common subexpression elimination if the function is called multiple times in one query. Optional parameter.

Returned value

The FixedString type value.

Usage example

SELECT generateULID()

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

Usage example if it is needed to generate multiple values in one row

SELECT generateULID(1), generateULID(2)

┌─generateULID(1)────────────┬─generateULID(2)────────────┐
│ 01GNB2SGG4RHKVNT9ZGA4FFMNP │ 01GNB2SGG4V0HMQVH4VBVPSSRB │
└────────────────────────────┴────────────────────────────┘

ULIDStringToDateTime

This function extracts the timestamp from a ULID.

Syntax

ULIDStringToDateTime(ulid[, timezone])

Arguments

• ulid — Input ULID. String or FixedString(26).
• timezone — Timezone name for the returned value (optional). String.

Returned value

• Timestamp with milliseconds precision. DateTime64(3).

Usage example

SELECT ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')

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

See Also

• UUID

ULIDStringToDateTime

Introduced in: v23.3

This function extracts the timestamp from a [ULID]((https://github.com/ulid/spec).

Syntax

ULIDStringToDateTime(ulid[, timezone])

Arguments

• ulid — Input ULID. String or FixedString(26)
• timezone — Optional. Timezone name for the returned value. String

Returned value

Timestamp with milliseconds precision. DateTime64(3)

Examples

Usage example

SELECT ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')

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

generateULID

Introduced in: v23.2

Generates a Universally Unique Lexicographically Sortable Identifier (ULID).

Syntax

generateULID([x])

Arguments

• x — Optional. An expression resulting in any of the supported data types. The resulting value is discarded, but the expression itself if used for bypassing common subexpression elimination if the function is called multiple times in one query. Any

Returned value

Returns a ULID. FixedString(26)

Examples

Usage example

SELECT generateULID()

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

Usage example if it is needed to generate multiple values in one row

SELECT generateULID(1), generateULID(2)

┌─generateULID(1)────────────┬─generateULID(2)────────────┐
│ 01GNB2SGG4RHKVNT9ZGA4FFMNP │ 01GNB2SGG4V0HMQVH4VBVPSSRB │
└────────────────────────────┴────────────────────────────┘