Fix for typo in UUIDv7 timestamp extraction
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
Attachments:
0001-Fix-constant-when-extracting-timestamp-from-UUIDv7.patchtext/x-patch; charset=US-ASCII; name=0001-Fix-constant-when-extracting-timestamp-from-UUIDv7.patchDownload+1-2
Hi Erik!
On 13 Aug 2025, at 11:05, Erik Nordström <erik@tigerdata.com> wrote:
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.
Wow, that's a very good level of proofreading! Yes, you are correct, it's must be US_PER_MS.
Anyway, attaching a patch to fix this typo.
LGTM.
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.
UUID might be formed by any external system, rand_a bits are not guaranteed to be non-random. Well, in some sense, reading time is reading random number, but anyway, we don't know for sure how those bits are used. And even in Postgres usage of sub-millisecond fractions depends on OS.
I think we can have a function in an extensions, that does pretend that UUID was generated by known algorithm. Or even SQL function to extract microseconds.
Thanks!
Best regards, Andrey Borodin.
On Wed, Aug 13, 2025 at 11:52 AM Andrey Borodin <x4mmm@yandex-team.ru>
wrote:
Hi Erik!
On 13 Aug 2025, at 11:05, Erik Nordström <erik@tigerdata.com> wrote:
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.Wow, that's a very good level of proofreading! Yes, you are correct, it's
must be US_PER_MS.Anyway, attaching a patch to fix this typo.
LGTM.
Ok, maybe a committer would like to commit this change immediately?
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.UUID might be formed by any external system, rand_a bits are not
guaranteed to be non-random. Well, in some sense, reading time is reading
random number, but anyway, we don't know for sure how those bits are used.
And even in Postgres usage of sub-millisecond fractions depends on OS.I think we can have a function in an extensions, that does pretend that
UUID was generated by known algorithm. Or even SQL function to extract
microseconds.
That's what I suspected. Thanks for clarifying.
Best,
Erik
On Thu, Aug 14, 2025 at 5:10 AM Erik Nordström <erik@tigerdata.com> wrote:
On Wed, Aug 13, 2025 at 11:52 AM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
Hi Erik!
On 13 Aug 2025, at 11:05, Erik Nordström <erik@tigerdata.com> wrote:
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.
Wow, that's a very good level of proofreading! Yes, you are correct, it's must be US_PER_MS.
Anyway, attaching a patch to fix this typo.
LGTM.
Ok, maybe a committer would like to commit this change immediately?
Thank you for the report! I'll push the patch to master and v18 shortly.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
On Fri, Aug 15, 2025 at 11:44 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Thu, Aug 14, 2025 at 5:10 AM Erik Nordström <erik@tigerdata.com> wrote:
On Wed, Aug 13, 2025 at 11:52 AM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
Hi Erik!
On 13 Aug 2025, at 11:05, Erik Nordström <erik@tigerdata.com> wrote:
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.
Wow, that's a very good level of proofreading! Yes, you are correct, it's must be US_PER_MS.
Anyway, attaching a patch to fix this typo.
LGTM.
Ok, maybe a committer would like to commit this change immediately?
Thank you for the report! I'll push the patch to master and v18 shortly.
Pushed.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com