Functions for Working with UUIDs - ClickHouse Documentation

UUIDv7 generation

The generated UUID contains a 48-bit timestamp in Unix milliseconds, followed by version “7” (4 bits), a counter (42 bits) to distinguish UUIDs within a millisecond (including a variant field “2”, 2 bits), and a random field (32 bits). For any given timestamp (unix_ts_ms), the counter starts at a random value and is incremented by 1 for each new UUID until the timestamp changes. In case the counter overflows, the timestamp field is incremented by 1 and the counter is reset to a random new start value. The UUID generation functions guarantee that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries.

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|                           unix_ts_ms                          |
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|          unix_ts_ms           |  ver  |   counter_high_bits   |
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|var|                   counter_low_bits                        |
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|                            rand_b                             |
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘

Snowflake ID generation

The generated Snowflake ID contains the current Unix timestamp in milliseconds (41 + 1 top zero bits), followed by a machine id (10 bits), and a counter (12 bits) to distinguish IDs within a millisecond. For any given timestamp (unix_ts_ms), the counter starts at 0 and is incremented by 1 for each new Snowflake ID until the timestamp changes. In case the counter overflows, the timestamp field is incremented by 1 and the counter is reset to 0.

The generated Snowflake IDs are based on the UNIX epoch 1970-01-01. While no standard or recommendation exists for the epoch of Snowflake IDs, implementations in other systems may use a different epoch, e.g. Twitter/X (2010-11-04) or Mastodon (2015-01-01).

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|0|                         timestamp                           |
├─┼                 ┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|                   |     machine_id    |    machine_seq_num    |
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘

UUIDNumToString

Introduced in: v1.1.0 Takes a binary representation of a UUID, with its format optionally specified by variant (Big-endian by default), and returns a string containing 36 characters in text format. Syntax

UUIDNumToString(binary[, variant])

Arguments

binary — Binary representation of a UUID. FixedString(16)
variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returned value Returns the UUID as a string. String Examples Usage example

Query

SELECT
    'a/<@];!~p{jTj={)' AS bytes,
    UUIDNumToString(toFixedString(bytes, 16)) AS uuid

Response

┌─bytes────────────┬─uuid─────────────────────────────────┐
│ a/<@];!~p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
└──────────────────┴──────────────────────────────────────┘

Microsoft variant

Query

SELECT
    '@</a;]~!p{jTj={)' AS bytes,
    UUIDNumToString(toFixedString(bytes, 16), 2) AS uuid

Response

┌─bytes────────────┬─uuid─────────────────────────────────┐
│ @</a;]~!p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
└──────────────────┴──────────────────────────────────────┘

UUIDStringToNum

Introduced in: v1.1.0 Accepts a string containing 36 characters in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, and returns a FixedString(16) as its binary representation, with its format optionally specified by variant (Big-endian by default). Syntax

UUIDStringToNum(string[, variant = 1])

Arguments

string — A string or fixed-string of 36 characters) String or FixedString(36)
variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returned value Returns the binary representation of string. FixedString(16) Examples Usage example

Query

SELECT
    '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid,
    UUIDStringToNum(uuid) AS bytes

Response

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ a/<@];!~p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

Microsoft variant

Query

SELECT
    '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid,
    UUIDStringToNum(uuid, 2) AS bytes

Response

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ @</a;]~!p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

UUIDToNum

Introduced in: v24.5.0 Accepts a UUID and returns its binary representation as a FixedString(16), with its format optionally specified by variant (Big-endian by default). This function replaces calls to two separate functions UUIDStringToNum(toString(uuid)) so no intermediate conversion from UUID to string is required to extract bytes from a UUID. Syntax

UUIDToNum(uuid[, variant = 1])

Arguments

uuid — UUID. String or FixedString
variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returned value Returns a binary representation of the UUID. FixedString(16) Examples Usage example

Query

SELECT
    toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
    UUIDToNum(uuid) AS bytes

Response

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ a/<@];!~p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

Microsoft variant

Query

SELECT
    toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
    UUIDToNum(uuid, 2) AS bytes

Response

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ @</a;]~!p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

UUIDv7ToDateTime

Introduced in: v24.5.0 Returns the timestamp component of a UUID version 7. Syntax

UUIDv7ToDateTime(uuid[, timezone])

Arguments

uuid — A UUID version 7. String
timezone — Optional. Timezone name for the returned value. String

Returned value Returns a timestamp with milliseconds precision. If the UUID is not a valid version 7 UUID, it returns 1970-01-01 00:00:00.000. DateTime64(3) Examples Usage example

Query

SELECT UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'))

Response

┌─UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'))─┐
│                                          2024-04-22 15:30:29.048 │
└──────────────────────────────────────────────────────────────────┘

With timezone

Query

SELECT UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'), 'America/New_York')

Response

┌─UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'), 'America/New_York')─┐
│                                                             2024-04-22 11:30:29.048 │
└─────────────────────────────────────────────────────────────────────────────────────┘

dateTime64ToSnowflakeID

Introduced in: v24.6.0 Converts a DateTime64 value to the first Snowflake ID at the giving time. Syntax

dateTime64ToSnowflakeID(value[, epoch])

Arguments

value — Date with time. DateTime64
epoch — Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. UInt*

Returned value Input value converted to UInt64 Examples simple

Query

SELECT dateTime64ToSnowflakeID(toDateTime64('2021-08-15 18:57:56', 3, 'Asia/Shanghai'))

Response

6832626394434895872

dateTimeToSnowflakeID

Introduced in: v24.6.0 Converts a DateTime value to the first Snowflake ID at the giving time. Syntax

dateTimeToSnowflakeID(value[, epoch])

Arguments

value — Date with time. DateTime
epoch — Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. UInt*

Returned value Input value converted to UInt64 Examples simple

Query

SELECT dateTimeToSnowflakeID(toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai'))

Response

6832626392367104000

dateTimeToUUIDv7

Introduced in: v25.8.0 Converts a DateTime value to a UUIDv7 at the given time. See section “UUIDv7 generation” for details on UUID structure, counter management, and concurrency guarantees.

As of September 2025, version 7 UUIDs are in draft status and their layout may change in future.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

dateTimeToUUIDv7(value)

Arguments

value — Date with time. DateTime

Returned value Returns a UUIDv7. UUID Examples Usage example

Query

SELECT dateTimeToUUIDv7(toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai'));

Response

┌─dateTimeToUUIDv7(toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai'))─┐
│ 018f05af-f4a8-778f-beee-1bedbc95c93b                                   │
└─────────────────────────────────────────────────────────────────────────┘

multiple UUIDs for the same timestamp

Query

SELECT dateTimeToUUIDv7(toDateTime('2021-08-15 18:57:56'));
SELECT dateTimeToUUIDv7(toDateTime('2021-08-15 18:57:56'));

Response

┌─dateTimeToUUIDv7(t⋯08-15 18:57:56'))─┐
│ 017b4b2d-7720-76ed-ae44-bbcc23a8c550 │
└──────────────────────────────────────┘
┌─dateTimeToUUIDv7(t⋯08-15 18:57:56'))─┐
│ 017b4b2d-7720-76ed-ae44-bbcf71ed0fd3 │
└──────────────────────────────────────┘

generateSnowflakeID

Introduced in: v24.6.0 Generates a Snowflake ID. Function generateSnowflakeID guarantees that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries. See section “Snowflake ID generation” for implementation details.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

generateSnowflakeID([expr, [machine_id]])

Arguments

expr — An arbitrary expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression has no effect on the returned Snowflake ID. Optional. - machine_id — A machine ID, the lowest 10 bits are used. Int64. Optional.

Returned value Returns the Snowflake ID. UInt64 Examples Usage example

Query

CREATE TABLE tab (id UInt64)
ENGINE = MergeTree()
ORDER BY tuple();

INSERT INTO tab SELECT generateSnowflakeID();

SELECT * FROM tab;

Response

┌──────────────────id─┐
│ 7199081390080409600 │
└─────────────────────┘

Multiple Snowflake IDs generated per row

Query

SELECT generateSnowflakeID(1), generateSnowflakeID(2);

Response

┌─generateSnowflakeID(1)─┬─generateSnowflakeID(2)─┐
│    7199081609652224000 │    7199081609652224001 │
└────────────────────────┴────────────────────────┘

With expression and a machine ID

Query

SELECT generateSnowflakeID('expr', 1);

Response

┌─generateSnowflakeID('expr', 1)─┐
│            7201148511606784002 │
└────────────────────────────────┘

generateUUIDv4

Introduced in: v1.1.0 Generates a version 4 UUID.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

generateUUIDv4([expr])

Arguments

expr — Optional. An arbitrary expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression has no effect on the returned UUID.

Returned value Returns a UUIDv4. UUID Examples Usage example

Query

SELECT generateUUIDv4(number) FROM numbers(3);

Response

┌─generateUUIDv4(number)───────────────┐
│ fcf19b77-a610-42c5-b3f5-a13c122f65b6 │
│ 07700d36-cb6b-4189-af1d-0972f23dc3bc │
│ 68838947-1583-48b0-b9b7-cf8268dd343d │
└──────────────────────────────────────┘

Common subexpression elimination

Query

SELECT generateUUIDv4(1), generateUUIDv4(1);

Response

┌─generateUUIDv4(1)────────────────────┬─generateUUIDv4(2)────────────────────┐
│ 2d49dc6e-ddce-4cd0-afb8-790956df54c1 │ 2d49dc6e-ddce-4cd0-afb8-790956df54c1 │
└──────────────────────────────────────┴──────────────────────────────────────┘

generateUUIDv7

Introduced in: v24.5.0 Generates a version 7 UUID. See section “UUIDv7 generation” for details on UUID structure, counter management, and concurrency guarantees.

As of September 2025, version 7 UUIDs are in draft status and their layout may change in future.

This function is non-deterministic: it can return different results for the same arguments.

Syntax

generateUUIDv7([expr])

Arguments

expr — Optional. An arbitrary expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression has no effect on the returned UUID. Any

Returned value Returns a UUIDv7. UUID Examples Usage example

Query

SELECT generateUUIDv7(number) FROM numbers(3);

Response

┌─generateUUIDv7(number)───────────────┐
│ 019947fb-5766-7ed0-b021-d906f8f7cebb │
│ 019947fb-5766-7ed0-b021-d9072d0d1e07 │
│ 019947fb-5766-7ed0-b021-d908dca2cf63 │
└──────────────────────────────────────┘

Common subexpression elimination

Query

SELECT generateUUIDv7(1), generateUUIDv7(1);

Response

┌─generateUUIDv7(1)────────────────────┬─generateUUIDv7(1)────────────────────┐
│ 019947ff-0f87-7d88-ace0-8b5b3a66e0c1 │ 019947ff-0f87-7d88-ace0-8b5b3a66e0c1 │
└──────────────────────────────────────┴──────────────────────────────────────┘

snowflakeIDToDateTime

Introduced in: v24.6.0 Returns the timestamp component of a Snowflake ID as a value of type DateTime. Syntax

snowflakeIDToDateTime(value[, epoch[, time_zone]])

Arguments

value — Snowflake ID. UInt64
epoch — Optional. Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. UInt*
time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value Returns the timestamp component of value. DateTime Examples Usage example

Query

SELECT snowflakeIDToDateTime(7204436857747984384) AS res

Response

┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

snowflakeIDToDateTime64

Introduced in: v24.6.0 Returns the timestamp component of a Snowflake ID as a value of type DateTime64. Syntax

snowflakeIDToDateTime64(value[, epoch[, time_zone]])

Arguments

value — Snowflake ID. UInt64
epoch — Optional. Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. UInt*
time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value Returns the timestamp component of value as a DateTime64 with scale = 3, i.e. millisecond precision. DateTime64 Examples Usage example

Query

SELECT snowflakeIDToDateTime64(7204436857747984384) AS res

Response

┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

toUUIDOrDefault

Introduced in: v21.1.0 Converts a String value to UUID type. If the conversion fails, returns a default UUID value instead of throwing an error. This function attempts to parse a string of 36 characters in the standard UUID format (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). If the string cannot be converted to a valid UUID, the function returns the provided default UUID value. Syntax

toUUIDOrDefault(string, default)

Arguments

string — String of 36 characters or FixedString(36) to be converted to UUID. - default — UUID value to be returned if the first argument cannot be converted to UUID type.

Returned value Returns the converted UUID if successful, or the default UUID if conversion fails. UUID Examples Successful conversion returns the parsed UUID

Query

SELECT toUUIDOrDefault('61f0c404-5cb3-11e7-907b-a6006ad3dba0', toUUID('59f0c404-5cb3-11e7-907b-a6006ad3dba0'));

Response

┌─toUUIDOrDefault('61f0c404-5cb3-11e7-907b-a6006ad3dba0', toUUID('59f0c404-5cb3-11e7-907b-a6006ad3dba0'))─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Failed conversion returns the default UUID

Query

SELECT toUUIDOrDefault('-----61f0c404-5cb3-11e7-907b-a6006ad3dba0', toUUID('59f0c404-5cb3-11e7-907b-a6006ad3dba0'));

Response

┌─toUUIDOrDefault('-----61f0c404-5cb3-11e7-907b-a6006ad3dba0', toUUID('59f0c404-5cb3-11e7-907b-a6006ad3dba0'))─┐
│ 59f0c404-5cb3-11e7-907b-a6006ad3dba0                                                                          │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

toUUIDOrNull

Introduced in: v20.12.0 Converts an input value to a value of type UUID but returns NULL in case of an error. Like toUUID but returns NULL instead of throwing an exception on conversion errors. Supported arguments:

String representations of UUID in standard format (8-4-4-4-12 hexadecimal digits).
String representations of UUID without hyphens (32 hexadecimal digits).

Unsupported arguments (return NULL):

Invalid string formats.
Non-string types.
Malformed UUIDs.

Syntax

toUUIDOrNull(x)

Arguments

x — A string representation of a UUID. String

Returned value Returns a UUID value if successful, otherwise NULL. UUID or NULL Examples Usage examples

Query

SELECT
    toUUIDOrNull('550e8400-e29b-41d4-a716-446655440000') AS valid_uuid,
    toUUIDOrNull('invalid-uuid') AS invalid_uuid

Response

┌─valid_uuid───────────────────────────┬─invalid_uuid─┐
│ 550e8400-e29b-41d4-a716-446655440000 │         ᴺᵁᴸᴸ │
└──────────────────────────────────────┴──────────────┘

​UUIDv7 generation

​Snowflake ID generation

​UUIDNumToString

​UUIDStringToNum

​UUIDToNum

​UUIDv7ToDateTime

​dateTime64ToSnowflakeID

​dateTimeToSnowflakeID

​dateTimeToUUIDv7

​generateSnowflakeID

​generateUUIDv4

​generateUUIDv7

​snowflakeIDToDateTime

​snowflakeIDToDateTime64

​toUUIDOrDefault

​toUUIDOrNull

UUIDv7 generation

Snowflake ID generation

UUIDNumToString

UUIDStringToNum

UUIDToNum

UUIDv7ToDateTime

dateTime64ToSnowflakeID

dateTimeToSnowflakeID

dateTimeToUUIDv7

generateSnowflakeID

generateUUIDv4

generateUUIDv7

snowflakeIDToDateTime

snowflakeIDToDateTime64

toUUIDOrDefault

toUUIDOrNull