On Mon Aug 3, 2026 at 7:21 PM UTC, Masahiko Sawada wrote: > On Wed, Jun 24, 2026 at 4:29 PM Masahiko Sawada <[email protected]> wrote: >> >> On Wed, Jun 24, 2026 at 1:10 PM Tristan Partin <[email protected]> wrote: >> > >> > 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. >> >> Thank you for the patch! >> >> The patch seems reasonable to me. I'll review the patch in depth. >> > > I've reviewed the v1 patches and the 0001 patch looks good to me. I > have one comment for the 0002 patch: > > + /* > + * 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. > + */ > > pgindent destroys these field tables. We need to tell pgindent to > leave the block alone by adding the dashed comment form: > > /*---------- > * UUIDv6 is a field-compatible reordering of UUIDv1 that stores the > ... > *---------- > */ > > Or I guess that this part doesn't necessarily need to be such a > format. Simply writing it like follow would work too: > > /* > * UUIDv1 stores the 60-bit Gregorian timestamp in three fields that > * are *not* in most-significant-first order (RFC 9562 sec. 5.1): > * octets 0-3 hold the least significant 32 bits, octets 4-5 the > * middle 16, and octet 6's low nibble plus octet 7 the most > * significant 12. Octet 6's high nibble is the version, masked off > * below. The shifts put each field back in its place. > */
Thanks for the review. I went with option 1 to fix the comments in the patch. I think it is a bit more readable than option 2. Included is a re-spin of the first patch, but it is equivalent to v1. I tested pgindent myself on v2, and it left the comment block alone. -- Tristan Partin PostgreSQL Contributors Team AWS (https://aws.amazon.com)
From a6a31ebf7a9bdf804692f405c5ee1b2cb3589ea4 Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Wed, 24 Jun 2026 19:52:40 +0000 Subject: [PATCH v2 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 89fd5781bcc..7b6181b38e7 100644 --- a/doc/src/sgml/func/func-uuid.sgml +++ b/doc/src/sgml/func/func-uuid.sgml @@ -136,7 +136,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 a9edceabab0..c0133c0b3e1 100644 --- a/src/backend/utils/adt/uuid.c +++ b/src/backend/utils/adt/uuid.c @@ -790,6 +790,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 36d54d2f33e..d542eb14b26 100644 --- a/src/test/regress/expected/uuid.out +++ b/src/test/regress/expected/uuid.out @@ -329,6 +329,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 944be956dff..54f0d8f8255 100644 --- a/src/test/regress/sql/uuid.sql +++ b/src/test/regress/sql/uuid.sql @@ -167,6 +167,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 bb2ad7fd103b90a84f6269a84acc1a6c9b9c17c6 Mon Sep 17 00:00:00 2001 From: Tristan Partin <[email protected]> Date: Wed, 24 Jun 2026 20:01:41 +0000 Subject: [PATCH v2 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 | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c index c0133c0b3e1..4d2b9af52c5 100644 --- a/src/backend/utils/adt/uuid.c +++ b/src/backend/utils/adt/uuid.c @@ -775,6 +775,19 @@ 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) @@ -792,6 +805,22 @@ 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) @@ -809,6 +838,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
