Hi, I think I found a small typo in the function that extracts a timestamp from a UUIDv7 (uuid_extract_timestamp). Unless I am mistaken, the constant US_PER_MS should be used instead of NS_PER_US when converting milliseconds to microseconds. Fortunately, these constants are the same so the calculation is still correct.
Anyway, attaching a patch to fix this typo. On a related note, I am wondering why this function doesn't extract and use the sub-millisecond information in the rand_a bits? These bits are added when generating the UUID, but they don't seem to be extracted. Hopefully somebody could shed some light on this and whether it would be a worthwhile addition. Thank you, - Erik
From 5c8b4070366581219bab0cdda27336bb0cea5844 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Erik=20Nordstr=C3=B6m?= <erik.nordst...@gmail.com> Date: Wed, 13 Aug 2025 09:41:08 +0200 Subject: [PATCH] Fix constant when extracting timestamp from UUIDv7 When extracting a timestamp from a UUIDv7, a conversion from milliseconds to microseconds is performed using the wrong constant NS_PER_US instead of US_PER_MS. Fortunately, these constants are both the same so the fix is mostly to avoid confusion. --- src/backend/utils/adt/uuid.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c index bce7309c183..a746388b073 100644 --- a/src/backend/utils/adt/uuid.c +++ b/src/backend/utils/adt/uuid.c @@ -752,7 +752,7 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS) + (((uint64) uuid->data[0]) << 40); /* convert ms to us, then adjust */ - ts = (TimestampTz) (tms * NS_PER_US) - + ts = (TimestampTz) (tms * US_PER_MS) - (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC; PG_RETURN_TIMESTAMPTZ(ts); -- 2.48.1
