UUIDv6 is the same as UUIDv1 with some fields reordered. We already supported UUIDv1, so let's add support for UUIDv6. Previously, calls to uuid_extract_timestamp() would return NULL for UUIDv6 values.
Patch 2 only adds comments. I think the bit manipulation is worthy of comments, but others may disagree. -- Tristan Partin PostgreSQL Contributors Team AWS (https://aws.amazon.com)
From c2801224505cf9615f0ec781b8b7b5ac4875199c Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Wed, 24 Jun 2026 19:52:40 +0000 Subject: [PATCH v1 1/2] Support UUIDv6 in uuid_extract_timestamp() UUIDv6 is a version of UUID that also includes a timestamp. It is actually a specialization of UUIDv1 with fields rearranged such that the most significant bits of the timestamp are in the most significant bits of the UUID, which aids in ordering. Signed-off-by: Tristan Partin <[email protected]> --- doc/src/sgml/func/func-uuid.sgml | 2 +- src/backend/utils/adt/uuid.c | 17 +++++++++++++++++ src/test/regress/expected/uuid.out | 6 ++++++ src/test/regress/sql/uuid.sql | 1 + 4 files changed, 25 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func/func-uuid.sgml b/doc/src/sgml/func/func-uuid.sgml index 2638e2bf85..4e47f84e96 100644 --- a/doc/src/sgml/func/func-uuid.sgml +++ b/doc/src/sgml/func/func-uuid.sgml @@ -130,7 +130,7 @@ </para> <para> Extracts a <type>timestamp with time zone</type> from a UUID of - version 1 or 7. For other versions, this function returns null. + version 1, 6, or 7. For other versions, this function returns null. Note that the extracted timestamp is not necessarily exactly equal to the time the UUID was generated; this depends on the implementation that generated the UUID. diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c index 6ee3752ac7..b6a7da68bb 100644 --- a/src/backend/utils/adt/uuid.c +++ b/src/backend/utils/adt/uuid.c @@ -738,6 +738,23 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMPTZ(ts); } + if (version == 6) + { + tms = ((uint64) uuid->data[0] << 52) + + ((uint64) uuid->data[1] << 44) + + ((uint64) uuid->data[2] << 36) + + ((uint64) uuid->data[3] << 28) + + ((uint64) uuid->data[4] << 20) + + ((uint64) uuid->data[5] << 12) + + (((uint64) uuid->data[6] & 0xf) << 8) + + ((uint64) uuid->data[7]); + + /* convert 100-ns intervals to us, then adjust */ + ts = (TimestampTz) (tms / 10) - + ((uint64) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC; + PG_RETURN_TIMESTAMPTZ(ts); + } + if (version == 7) { tms = (uuid->data[5]) diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out index 9c5dda9e9a..f1beb096f5 100644 --- a/src/test/regress/expected/uuid.out +++ b/src/test/regress/expected/uuid.out @@ -300,6 +300,12 @@ SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday t (1 row) +SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v6 + ?column? +---------- + t +(1 row) + SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v7 ?column? ---------- diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql index 8cc2ad4061..e0d01f1499 100644 --- a/src/test/regress/sql/uuid.sql +++ b/src/test/regress/sql/uuid.sql @@ -151,6 +151,7 @@ SELECT uuid_extract_version(uuidv7()); -- 7 -- timestamp SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v1 +SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v6 SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v7 SELECT uuid_extract_timestamp(gen_random_uuid()); -- null SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null -- Tristan Partin https://tristan.partin.io
From 9a6b6ca218d03cf2cf6c18faa32010feca635f21 Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Wed, 24 Jun 2026 20:01:41 +0000 Subject: [PATCH v1 2/2] Add comments to uuid_extract_timestamp() Reading bit manipulation can be tough, when you don't have the context of what it is trying to do. The added comments should help future readers understand what is going on a bit better. Signed-off-by: Tristan Partin <[email protected]> --- src/backend/utils/adt/uuid.c | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c index b6a7da68bb..4673389a49 100644 --- a/src/backend/utils/adt/uuid.c +++ b/src/backend/utils/adt/uuid.c @@ -723,6 +723,18 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS) if (version == 1) { + /* + * UUIDv1 splits the 60-bit Gregorian timestamp into three fields that + * are *not* stored most-significant-first (see RFC 9562 sec. 5.1): + * + * time_low (bits 0-31) octets 0-3, the least significant 32 bits + * time_mid (bits 32-47) octets 4-5, the middle 16 bits + * time_high (bits 48-59) octet 6 low nibble + octet 7, the most + * significant 12 bits (octet 6 high nibble + * holds the version and is masked off) + * + * Reassemble the timestamp by shifting each field back to its place. + */ tms = ((uint64) uuid->data[0] << 24) + ((uint64) uuid->data[1] << 16) + ((uint64) uuid->data[2] << 8) @@ -740,6 +752,21 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS) if (version == 6) { + /* + * UUIDv6 is a field-compatible reordering of UUIDv1 that stores the + * 60-bit Gregorian timestamp most-significant-first (see RFC 9562 + * sec. 5.6): + * + * time_high (bits 28-59) octets 0-3, the most significant 32 bits + * time_mid (bits 12-27) octets 4-5, the middle 16 bits + * time_low (bits 0-11) octet 6 low nibble + octet 7, the least + * significant 12 bits (octet 6 high nibble + * holds the version and is masked off) + * + * Note that time_mid is adjacent to time_low (shifted by 12, not 16): + * the version nibble splits the field boundary, so time_low is only + * 12 bits wide and the surrounding shifts must account for that. + */ tms = ((uint64) uuid->data[0] << 52) + ((uint64) uuid->data[1] << 44) + ((uint64) uuid->data[2] << 36) @@ -757,6 +784,12 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS) if (version == 7) { + /* + * UUIDv7 stores a 48-bit Unix timestamp in milliseconds (unix_ts_ms) + * most-significant-first in octets 0-5 (see RFC 9562 sec. 5.7). There + * is no version nibble inside this field, so the bytes reassemble at + * clean 8-bit boundaries. + */ tms = (uuid->data[5]) + (((uint64) uuid->data[4]) << 8) + (((uint64) uuid->data[3]) << 16) -- Tristan Partin https://tristan.partin.io
