ReferenceSQL ReferenceFunctions

UUID

UUID functions reference.

UUIDNumToString

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

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

Microsoft variant

SELECT
    '@</a;]~!p{jTj={)' AS bytes,
    UUIDNumToString(toFixedString(bytes, 16), 2) AS uuid
┌─bytes────────────┬─uuid─────────────────────────────────┐
│ @</a;]~!p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
└──────────────────┴──────────────────────────────────────┘

Introduced in version 1.1.

UUIDStringToNum

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

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

Microsoft variant

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

Introduced in version 1.1.

UUIDToNum

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

Returned value

Returns a binary representation of the UUID. FixedString(16)

Examples

Usage example

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

Microsoft variant

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

Introduced in version 24.5.

UUIDv7ToDateTime

Returns the timestamp component of a UUID version 7.

Syntax

UUIDv7ToDateTime(uuid[, timezone])

Arguments

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

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

With timezone

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

Introduced in version 24.5.

dateTime64ToSnowflake

Deprecated

:::warning This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.

Please use function dateTime64ToSnowflakeID instead. :::

Converts a DateTime64 to the first Snowflake ID at the giving time.

Syntax

dateTime64ToSnowflake(value)

Arguments

Returned value

Returns the input value converted as the first Snowflake ID at that time. Int64

Examples

Usage example

WITH toDateTime64('2021-08-15 18:57:56.492', 3, 'Asia/Shanghai') AS dt64 SELECT dateTime64ToSnowflake(dt64);
┌─dateTime64ToSnowflake(dt64)─┐
│         1426860704886947840 │
└─────────────────────────────┘

Introduced in version 21.10.

dateTime64ToSnowflakeID

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

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

Introduced in version 24.6.

dateTimeToSnowflake

Deprecated

:::warning This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.

Please use function dateTimeToSnowflakeID instead. :::

Converts a DateTime value to the first Snowflake ID at the giving time.

Syntax

dateTimeToSnowflake(value)

Arguments

Returned value

Returns the input value as the first Snowflake ID at that time. Int64

Examples

Usage example

WITH toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai') AS dt SELECT dateTimeToSnowflake(dt);
┌─dateTimeToSnowflake(dt)─┐
│     1426860702823350272 │
└─────────────────────────┘

Introduced in version 21.10.

dateTimeToSnowflakeID

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

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

Introduced in version 24.6.

dateTimeToUUIDv7

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.

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

Syntax

dateTimeToUUIDv7(value)

Arguments

Returned value

Returns a UUIDv7. UUID

Examples

Usage example

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

multiple UUIDs for the same timestamp

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

Introduced in version 25.9.

generateSnowflakeID

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.

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

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

INSERT INTO tab SELECT generateSnowflakeID();

SELECT * FROM tab;
┌──────────────────id─┐
│ 7199081390080409600 │
└─────────────────────┘

Multiple Snowflake IDs generated per row

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

With expression and a machine ID

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

Introduced in version 24.6.

generateUUIDv4

Generates a version 4 UUID.

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

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

Common subexpression elimination

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

Introduced in version 1.1.

generateUUIDv7

Generates a version 7 UUID.

See section "UUIDv7 generation" for details on UUID structure, counter management, and concurrency guarantees.

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

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

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

Common subexpression elimination

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

Introduced in version 24.5.

snowflakeIDToDateTime

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

SELECT snowflakeIDToDateTime(7204436857747984384) AS res
┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

Introduced in version 24.6.

snowflakeIDToDateTime64

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

SELECT snowflakeIDToDateTime64(7204436857747984384) AS res
┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

Introduced in version 24.6.

snowflakeToDateTime

Deprecated

:::warning This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.

Please use function snowflakeIDToDateTime instead. :::

Extracts the timestamp component of a Snowflake ID in DateTime format.

Syntax

snowflakeToDateTime(value[, time_zone])

Arguments

  • value — Snowflake ID. Int64
  • 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

SELECT snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC');
┌─snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC')─┐
│                                              2021-08-15 10:57:56 │
└──────────────────────────────────────────────────────────────────┘

Introduced in version 21.10.

snowflakeToDateTime64

Deprecated

:::warning This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.

Please use function snowflakeIDToDateTime64 instead. :::

Extracts the timestamp component of a Snowflake ID in DateTime64 format.

Syntax

snowflakeToDateTime64(value[, time_zone])

Arguments

  • value — Snowflake ID. Int64
  • time_zone — Optional. Timezone. The function parses time_string according to the timezone. String

Returned value

Returns the timestamp component of value. DateTime64(3)

Examples

Usage example

SELECT snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC');
┌─snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC')─┐
│                                            2021-08-15 10:58:19.841 │
└────────────────────────────────────────────────────────────────────┘

Introduced in version 21.10.

toUUIDOrDefault

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

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

Failed conversion returns the default UUID

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

Introduced in version 21.1.

toUUIDOrNull

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

SELECT
    toUUIDOrNull('550e8400-e29b-41d4-a716-446655440000') AS valid_uuid,
    toUUIDOrNull('invalid-uuid') AS invalid_uuid
┌─valid_uuid───────────────────────────┬─invalid_uuid─┐
│ 550e8400-e29b-41d4-a716-446655440000 │         ᴺᵁᴸᴸ │
└──────────────────────────────────────┴──────────────┘

Introduced in version 20.12.

URL

URL functions reference.

Aggregate Functions

Reference for aggregate SQL functions.

On this page

UUIDNumToStringUUIDStringToNumUUIDToNumUUIDv7ToDateTimedateTime64ToSnowflakedateTime64ToSnowflakeIDdateTimeToSnowflakedateTimeToSnowflakeIDdateTimeToUUIDv7generateSnowflakeIDgenerateUUIDv4generateUUIDv7snowflakeIDToDateTimesnowflakeIDToDateTime64snowflakeToDateTimesnowflakeToDateTime64toUUIDOrDefaulttoUUIDOrNull