uuidv7 improperly accepts dates before 1970-01-01

On 25 Apr 2026, at 05:19, Christophe Pettus <xof@thebuild.com> wrote:

"Don't do that" is one answer, but for good hygiene, here's a patch that adds a < 0 check and a regression test.

Hi Christophe!

We intentionally left ability to overflow unix_ts_ms bits. In some cases one might want to
intentionally break time locality by using construction like SELECT uuidv7(INTERVAL '1000 years' * shard_id);
This will give time locality for UUIDs generated on each shard. We consulted with RFC authors
about this feature, and they confirmed that shifting time is compliant with RFC wording.
We wrote the specific test that ensures vast space for shift, but not unlimited.

Time shifting would become a footgun if we throw an exception when overflown.
If you use SELECT uuidv7(INTERVAL '-1000 years'); for generating identifiers, they will still be unique and
time-local, and more over - they will be ascending for a single backend. So no documented guarantees
are broken.

Thank you!

Best regards, Andrey Borodin.

Hi, Andrey,

Thanks for the response! I'm moving it to -hackers since it's not really a bug related conversation at this point. (resending with the right list this time!)

On Apr 25, 2026, at 05:26, Andrey Borodin <x4mmm@yandex-team.ru> wrote:

We consulted with RFC authors
about this feature, and they confirmed that shifting time is compliant with RFC wording.

Time shifting doesn't automatically imply allowing a pre-epoch input time to construct a UUIDv7, though, just that you can construct a UUIDv7 with something other than wall-clock time.

We wrote the specific test that ensures vast space for shift, but not unlimited.

That's another problem: the API gives the impression of a much larger space than actually exists.

# select uuidv7('100000 years'::interval); # ~11.2 x total time range in a UUID v7.
uuidv7
--------------------------------------
37b45c74-469d-7e1b-9397-1a971a99ab2b
(1 row)

At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.

Time shifting would become a footgun if we throw an exception when overflown.

I don't understand why. If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution them not to do that rather than permit the wraparound.

And is anyone actually doing this? Using a very large interval with a large enough number of shards that wraparound is a real possibility? (In that case, I'd argue they should construct the 48 bit field directly rather than kind of dancing around it by using a time shift.)

Hi,

On Mon, Apr 27, 2026 at 3:51 PM Christophe Pettus <xof@thebuild.com> wrote:

We wrote the specific test that ensures vast space for shift, but not unlimited.

That's another problem: the API gives the impression of a much larger space than actually exists.

# select uuidv7('100000 years'::interval); # ~11.2 x total time range in a UUID v7.
uuidv7
--------------------------------------
37b45c74-469d-7e1b-9397-1a971a99ab2b
(1 row)

Fair point.

At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.

I think that if we add a lower-bound check as the proposed patch does
an upper-bound check should also be added.

Time shifting would become a footgun if we throw an exception when overflown.

I don't understand why. If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution them not to do that rather than permit the wraparound.

I guess that monotonicity could easily be violated depending on how
users shift the wall-clock. Taking Andrey's example, if they use
something like uuidv7('-10 years' * shard_id), the monotonicity would
be broken with just 6 shards.

I guess it would be safer to raise an error in such cases rather than
silently allowing wraparound. Otherwise, users might only realize that
their UUIDv7 values are no longer sortable years down the road, which
would be disastrous. Moreover, raising an error would be consistent
with how PostgreSQL natively handles timestamp + interval overflows.

That said, while I am leaning toward introducing boundary checks, we
should carefully consider this change since it could potentially break
existing applications that rely on the current behavior of
uuidv7(interval).

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hello Masahiko,

On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

I guess it would be safer to raise an error in such cases rather than
silently allowing wraparound. Otherwise, users might only realize that
their UUIDv7 values are no longer sortable years down the road, which
would be disastrous. Moreover, raising an error would be consistent
with how PostgreSQL natively handles timestamp + interval overflows.

+1. I ran into the same issue while testing, specifically,
uuidv7('infinity'::interval) overflows int64 during the epoch
conversion and produces a UUID with an incorrect timestamp.
There's no valid use case for infinity as a shift offset.

I have a small patch that adds a TIMESTAMP_NOT_FINITE check after
timestamptz_pl_interval(), which catches both infinity and
-infinity. Happy to extend it to also cover the 48-bit upper/lower
bound checks if we agree on the direction.

Thanks,
Baji Shaik

On Wed, May 27, 2026 at 6:01 PM Baji Shaik <baji.pgdev@gmail.com> wrote:

Hello Masahiko,

On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

I guess it would be safer to raise an error in such cases rather than
silently allowing wraparound. Otherwise, users might only realize that
their UUIDv7 values are no longer sortable years down the road, which
would be disastrous. Moreover, raising an error would be consistent
with how PostgreSQL natively handles timestamp + interval overflows.

+1. I ran into the same issue while testing, specifically,
uuidv7('infinity'::interval) overflows int64 during the epoch
conversion and produces a UUID with an incorrect timestamp.
There's no valid use case for infinity as a shift offset.

Yeah, I think we should reject such cases at least.

I have a small patch that adds a TIMESTAMP_NOT_FINITE check after
timestamptz_pl_interval(), which catches both infinity and
-infinity. Happy to extend it to also cover the 48-bit upper/lower
bound checks if we agree on the direction.

I think we should go ahead and add both upper and lower bound checks,
barring objections.

The current behavior silently produces UUIDs with nonsensical
timestamps when the interval causes the result to go before the Unix
epoch or beyond what 48 bits can represent. Regarding backward
compatibility: this change would affect applications using
uuidv7(interval) with values that cause the resulting timestamp to
fall outside the representable range. But I guess that in practice,
any such application is likely already getting incorrect results due
to wraparound, so the risk of breaking working use cases seems very
low.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

I think we should go ahead and add both upper and lower bound checks,
barring objections.

Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:

0001 - Reject timestamps before the Unix epoch (lower bound)
0002 - Reject infinite intervals
0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Christophe's original v1 covered the pre-epoch case; 0001 is
essentially the same fix with slightly different wording. I have
included it here so the series is self-contained and applies
cleanly on HEAD. Happy to drop it in favor of Christophe's
version if you prefer that.

The infinity check (0002) goes before the epoch conversion so
that uuidv7('infinity'::interval) gets a clear "infinite timestamps"
message rather than falling through to the pre-epoch check
with a confusing detail.

All three use ERRCODE_DATETIME_VALUE_OUT_OF_RANGE with errdetail.

Thanks,
Baji Shaik.

On Fri, Jun 12, 2026 at 5:34 PM Baji Shaik <baji.pgdev@gmail.com> wrote:

On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

I think we should go ahead and add both upper and lower bound checks,
barring objections.

Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:

0001 - Reject timestamps before the Unix epoch (lower bound)
0002 - Reject infinite intervals
0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Added to the commitfest: https://commitfest.postgresql.org/patch/6935/

On Fri, Jun 12, 2026 at 3:35 PM Baji Shaik <baji.pgdev@gmail.com> wrote:

On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

I think we should go ahead and add both upper and lower bound checks,
barring objections.

Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:

0001 - Reject timestamps before the Unix epoch (lower bound)
0002 - Reject infinite intervals
0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Christophe's original v1 covered the pre-epoch case; 0001 is
essentially the same fix with slightly different wording. I have
included it here so the series is self-contained and applies
cleanly on HEAD. Happy to drop it in favor of Christophe's
version if you prefer that.

The infinity check (0002) goes before the epoch conversion so
that uuidv7('infinity'::interval) gets a clear "infinite timestamps"
message rather than falling through to the pre-epoch check
with a confusing detail.

All three use ERRCODE_DATETIME_VALUE_OUT_OF_RANGE with errdetail.

Thank you for creating the patches!

Here are some review comments:

+   /*
+    * Reject infinite intervals.  timestamptz_pl_interval() can produce an
+    * infinite timestamp when the input interval is infinite, and converting
+    * that to a Unix epoch value would overflow.
+    */
+   if (TIMESTAMP_NOT_FINITE(ts))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 does not support infinite
timestamps.")));
+

I think we can do this check earlier, like before shifting the
timestamp, and we can mention in the doc that we don't accept
'infinity' and '-infinity' values.

---
+   /*
+    * The UUID version 7 timestamp field is 48 bits wide, storing
+    * milliseconds since the Unix epoch.  Reject timestamps that would
+    * overflow this field (dates beyond approximately year 10889).
+    */
+   if (us / US_PER_MS > (int64) 0xFFFFFFFFFFFF)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 does not support timestamps
beyond approximately year 10889.")));

Please use INT64CONST() instead.

---
I think we need to mention in the doc that timestamp shifting beyond
the range UUIDv7 can support is not accepted.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Fri Jun 12, 2026 at 10:35 PM UTC, Baji Shaik wrote:

On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

I think we should go ahead and add both upper and lower bound checks,
barring objections.

Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:

0001 - Reject timestamps before the Unix epoch (lower bound)
0002 - Reject infinite intervals
0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Christophe's original v1 covered the pre-epoch case; 0001 is
essentially the same fix with slightly different wording. I have
included it here so the series is self-contained and applies
cleanly on HEAD. Happy to drop it in favor of Christophe's
version if you prefer that.

The infinity check (0002) goes before the epoch conversion so
that uuidv7('infinity'::interval) gets a clear "infinite timestamps"
message rather than falling through to the pre-epoch check
with a confusing detail.

All three use ERRCODE_DATETIME_VALUE_OUT_OF_RANGE with errdetail.

Thanks,
Baji Shaik.

+-- uuidv7(interval) rejects timestamps before the Unix epoch
+SELECT uuidv7('-1000 years'::interval);
+ERROR:  timestamp out of range for UUID version 7
+DETAIL:  UUID version 7 does not support timestamps before the Unix epoch.

You might want to steal the test in my patch. You never know if Postgres
will be around in 3070, and then this test will start to fail. Slightly
joking... lol

+-- uuidv7(interval) rejects timestamps that overflow the 48-bit field
+SELECT uuidv7('8920 years'::interval);
+ERROR:  timestamp out of range for UUID version 7
+DETAIL:  UUID version 7 does not support timestamps beyond approximately year 10889.

I think it might better to derive the interval similar to what I am
suggesting in my first comment.

Neither comment should block merging, and feel free to completely ignore
them. Just personal preference on my part.

Good work. Great minds think alike!

--
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

On Jun 24, 2026, at 10:46, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
I think we need to mention in the doc that timestamp shifting beyond
the range UUIDv7 can support is not accepted.

I believe the doc patch in my original patch included that admonition. Please feel free to grab it if you'd like.

Hello!

`us` can overflow and while it still results in an error, it is in the wrong direction:

SELECT uuidv7('292230 years'::interval);
ERROR: timestamp out of range for UUID version 7
DETAIL: UUID version 7 does not support timestamps before the Unix epoch.

Thank you Masahiko, Tristan, Christophe, and Zsolt for the reviews and
feedback. Addressing the feedback in a single email.

Attached v2 addressing the feedback:

- Moved infinity check before timestamp arithmetic [Masahiko]
- Used INT64CONST() for the 48-bit constant [Masahiko]
- Added documentation for the valid timestamp range [Masahiko, Christophe]
- Added a test for '292230 years' to cover the overflow path
caught by pg_add_s64_overflow() [Zsolt Parragi]

0001 - Reject infinite intervals
0002 - Reject pre-epoch timestamps (with overflow-safe epoch conversion)
0003 - Reject timestamps beyond the 48-bit limit

Let me know if I have missed anything.

Thanks,
Baji Shaik.