Functions for Working with UUIDs¶

generateUUIDv4¶

Generates a version 4 UUID.

Syntax

generateUUIDv4([expr])

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 UUID. Optional.

Returned value

A value of type UUIDv4.

generateUUIDv7¶

Generates a version 7 UUID.

The generated UUID contains the current Unix timestamp in milliseconds (48 bits), followed by version "7" (4 bits), a counter (42 bit) to distinguish UUIDs within a millisecond (including a variant field "2", 2 bit), 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.

Function generateUUIDv7 guarantees 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                             |
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘

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

Syntax

generateUUIDv7([expr])

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 UUID. Optional.

Returned value

A value of type UUIDv7.

empty¶

Checks whether the input UUID is empty.

Syntax

empty(UUID)

The UUID is considered empty if it contains all zeros (zero UUID).

The function also works for Arrays and Strings.

Arguments

x: A UUID. UUID.

Returned value

Returns 1 for an empty UUID or 0 for a non-empty UUID. UInt8.

Example

To generate the UUID value, Tinybird provides the generateUUIDv4 function.

Query:

SELECT empty(generateUUIDv4())

Result:

┌─empty(generateUUIDv4())─┐
│                       0 │
└─────────────────────────┘

notEmpty¶

Checks whether the input UUID is non-empty.

Syntax

notEmpty(UUID)

The UUID is considered empty if it contains all zeros (zero UUID).

The function also works for Arrays or Strings.

Arguments

x: A UUID. UUID.

Returned value

Returns 1 for a non-empty UUID or 0 for an empty UUID. UInt8.

Example

To generate the UUID value, Tinybird provides the generateUUIDv4 function.

Query:

SELECT notEmpty(generateUUIDv4())

Result:

┌─notEmpty(generateUUIDv4())─┐
│                          1 │
└────────────────────────────┘

toUUID¶

Converts a value of type String to a UUID.

toUUID(string)

Returned value

The UUID type value.

Usage example

SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid

Result:

┌─────────────────────────────────uuid─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │
└──────────────────────────────────────┘

toUUIDOrDefault¶

Arguments

string: String of 36 characters or FixedString(36). String.
default: UUID to be used as the default if the first argument cannot be converted to a UUID type. UUID.

Returned value

UUID

toUUIDOrDefault(string, default)

Returned value

The UUID type value.

Usage example

This first example returns the first argument converted to a UUID type as it can be converted:

SELECT toUUIDOrDefault('61f0c404-5cb3-11e7-907b-a6006ad3dba0', cast('59f0c404-5cb3-11e7-907b-a6006ad3dba0' as UUID))

Result:

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

This second example returns the second argument (the provided default UUID) as the first argument cannot be converted to a UUID type:

SELECT toUUIDOrDefault('-----61f0c404-5cb3-11e7-907b-a6006ad3dba0', cast('59f0c404-5cb3-11e7-907b-a6006ad3dba0' as UUID))

Result:

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

toUUIDOrNull¶

Takes an argument of type String and tries to parse it into UUID. If failed, returns NULL.

toUUIDOrNull(string)

Returned value

The Nullable(UUID) type value.

Usage example

SELECT toUUIDOrNull('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS uuid

Result:

┌─uuid─┐
│ ᴺᵁᴸᴸ │
└──────┘

toUUIDOrZero¶

It takes an argument of type String and tries to parse it into UUID. If failed, returns zero UUID.

toUUIDOrZero(string)

Returned value

The UUID type value.

Usage example

SELECT toUUIDOrZero('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS uuid

Result:

┌─────────────────────────────────uuid─┐
│ 00000000-0000-0000-0000-000000000000 │
└──────────────────────────────────────┘

UUIDStringToNum¶

Accepts 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 of 36 characters or FixedString
variant: Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

FixedString(16)

Usage example

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

Result:

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

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

Result:

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

UUIDNumToString¶

Accepts binary containing 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 = 1])

Arguments

binary: FixedString(16) as a binary representation of a UUID.
variant: Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

String.

Usage example

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

Result:

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

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

Result:

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

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

uuid: UUID.
variant: Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

The binary representation of the UUID.

Usage example

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

Result:

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

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

Result:

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

UUIDv7ToDateTime¶

Returns the timestamp component of a UUID version 7.

Syntax

UUIDv7ToDateTime(uuid[, timezone])

Arguments

uuid: UUID of version 7.
timezone: Timezone name for the returned value (optional). String.

Returned value

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).

Usage example

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

Result:

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

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

Result:

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

generateSnowflakeID¶

Generates a Snowflake ID.

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.

Function generateSnowflakeID guarantees that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries.

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    |
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘

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

A value of type UInt64.

snowflakeToDateTime¶

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.

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

Syntax

snowflakeToDateTime(value[, time_zone])

Arguments

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

Returned value

The timestamp component of value as a DateTime value.

Example

Query:

SELECT snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC')

Result:


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

snowflakeToDateTime64¶

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.

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

Syntax

snowflakeToDateTime64(value[, time_zone])

Arguments

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

Returned value

The timestamp component of value as a DateTime64 with scale = 3, i.e. millisecond precision.

Example

Query:

SELECT snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC')

Result:


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

dateTimeToSnowflake¶

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.

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

Syntax

dateTimeToSnowflake(value)

Arguments

value: Date with time. DateTime.

Returned value

Input value converted to the Int64 data type as the first Snowflake ID at that time.

Example

Query:

WITH toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai') AS dt SELECT dateTimeToSnowflake(dt)

Result:

┌─dateTimeToSnowflake(dt)─┐
│     1426860702823350272 │
└─────────────────────────┘

dateTime64ToSnowflake¶

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.

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

Syntax

dateTime64ToSnowflake(value)

Arguments

value: Date with time. DateTime64.

Returned value

Input value converted to the Int64 data type as the first Snowflake ID at that time.

Example

Query:

WITH toDateTime64('2021-08-15 18:57:56.492', 3, 'Asia/Shanghai') AS dt64 SELECT dateTime64ToSnowflake(dt64)

Result:

┌─dateTime64ToSnowflake(dt64)─┐
│         1426860704886947840 │
└─────────────────────────────┘

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 - 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. Optional. UInt*.
time_zone: Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

The timestamp component of value as a DateTime value.

Example

Query:

SELECT snowflakeIDToDateTime(7204436857747984384) AS res

Result:

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

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 - 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. Optional. UInt*.
time_zone: Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

The timestamp component of value as a DateTime64 with scale = 3, i.e. millisecond precision.

Example

Query:

SELECT snowflakeIDToDateTime64(7204436857747984384) AS res

Result:

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

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. Optional. UInt*.

Returned value

Input value converted to UInt64 as the first Snowflake ID at that time.

Example

Query:

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

Result:

┌──────────────────dt─┬─────────────────res─┐
│ 2021-08-15 18:57:56 │ 6832626392367104000 │
└─────────────────────┴─────────────────────┘

dateTime64ToSnowflakeID¶

Convert a DateTime64 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. Optional. UInt*.

Returned value

Input value converted to UInt64 as the first Snowflake ID at that time.

Example

Query:

SELECT toDateTime('2021-08-15 18:57:56.493', 3, 'Asia/Shanghai') AS dt, dateTime64ToSnowflakeID(dt) AS res

Result:

┌──────────────────────dt─┬─────────────────res─┐
│ 2021-08-15 18:57:56.493 │ 6832626394434895872 │
└─────────────────────────┴─────────────────────┘

Data Types

Table engines

Functions

Functions for Working with UUIDs¶

generateUUIDv4¶

generateUUIDv7¶

empty¶

notEmpty¶

toUUID¶

toUUIDOrDefault¶

toUUIDOrNull¶

toUUIDOrZero¶

UUIDStringToNum¶

UUIDNumToString¶

UUIDToNum¶

UUIDv7ToDateTime¶

generateSnowflakeID¶

snowflakeToDateTime¶

snowflakeToDateTime64¶

dateTimeToSnowflake¶

dateTime64ToSnowflake¶

snowflakeIDToDateTime¶

snowflakeIDToDateTime64¶

dateTimeToSnowflakeID¶

dateTime64ToSnowflakeID¶