From c2801224505cf9615f0ec781b8b7b5ac4875199c Mon Sep 17 00:00:00 2001 From: Tristan Partin 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 --- 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 @@ Extracts a timestamp with time zone 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