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.

Hello,

At Wed, 24 Jun 2026 18:22:20 -0500, Baji Shaik <baji.pgdev@gmail.com> wrote in

Let me know if I have missed anything.

The checks themselves seem reasonable to me. However, I do have one
comment about the error reporting.

The checks themselves seem reasonable to me. However, I'm not sure the
various cases need separate error messages.

From a user's perspective, it seems sufficient to know that the
shifted timestamp falls outside the range supported by UUID v7. As a
translator, I'm not particularly enthusiastic about adding more
message variants when the distinction is not particularly useful to
users.

Regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Wed, Jun 24, 2026 at 9:19 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

From a user's perspective, it seems sufficient to know that the
shifted timestamp falls outside the range supported by UUID v7. As a
translator, I'm not particularly enthusiastic about adding more
message variants when the distinction is not particularly useful to
users.

Thanks for the feedback, Kyotaro. Good point. Attached v3 with all boundary
checks
using a single shared errdetail:

"UUID version 7 supports timestamps from 1970-01-01 to approximately year
10889."

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

I prefer keeping them as 3 patches since each addresses a distinct
failure mode and is easier to review/bisect independently. That said,
since 0002 and 0003 now share the same errdetail and are logically
the same validation (timestamp outside valid range), I'm happy to
merge them into one patch for v4 if preferred.

Thanks,
Baji Shaik.

Thank you for the new version.

At Thu, 25 Jun 2026 08:47:27 -0500, Baji Shaik <baji.pgdev@gmail.com> wrote in

I prefer keeping them as 3 patches since each addresses a distinct
failure mode and is easier to review/bisect independently. That said,
since 0002 and 0003 now share the same errdetail and are logically
the same validation (timestamp outside valid range), I'm happy to
merge them into one patch for v4 if preferred.

Personally, I still feel that providing the UUIDv7 timestamp domain in
an errdetail is a bit excessive. Even for infinity, I tend to think of
the issue as simply being outside the representable range, so I'm not
sure that a separate message is necessary.

That said, this feels like a matter of taste, so it would probably be
useful to hear other opinions.

Regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Thu, Jun 25, 2026 at 6:47 AM Baji Shaik <baji.pgdev@gmail.com> wrote:

On Wed, Jun 24, 2026 at 9:19 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:

From a user's perspective, it seems sufficient to know that the
shifted timestamp falls outside the range supported by UUID v7. As a
translator, I'm not particularly enthusiastic about adding more
message variants when the distinction is not particularly useful to
users.

Thanks for the feedback, Kyotaro. Good point. Attached v3 with all boundary checks
using a single shared errdetail:

"UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889."

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

I prefer keeping them as 3 patches since each addresses a distinct
failure mode and is easier to review/bisect independently. That said,
since 0002 and 0003 now share the same errdetail and are logically
the same validation (timestamp outside valid range), I'm happy to
merge them into one patch for v4 if preferred.

Thank you for updating the patch! I have one comment:

+   /*
+    * Convert a TimestampTz value back to a UNIX epoch timestamp.  Use
+    * overflow-safe addition since large intervals can exceed int64 range.
+    */
+   if (pg_add_s64_overflow(ts,
+                           (int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+                           SECS_PER_DAY * USECS_PER_SEC,
+                           &us))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));

It does the range validation on the Unix-epoch value, after converting
back from the shifted PostgreSQL-epoch timestamp. Can we compare
pre-compute the Unix-epoch timestamp resentable window in
PostgreSQL-epoch units against the shifted PostgreSQL-epoch timestamp
before converting it back to the Unix epoch? That way, we don't need
to worry about the overflow.

---
+   /* UUID v7 uses an unsigned 48-bit millisecond field; reject pre-epoch */
+   if (us < 0)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));
+
+   /* Reject timestamps beyond the 48-bit millisecond field maximum */
+   if (us / US_PER_MS > (INT64CONST(1) << 48) - 1)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));

Let's merge these two if statements as they use the same error message.

Regards,

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

On Mon, Jun 29, 2026 at 6:18 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

It does the range validation on the Unix-epoch value, after converting
back from the shifted PostgreSQL-epoch timestamp. Can we compare
pre-compute the Unix-epoch timestamp resentable window in
PostgreSQL-epoch units against the shifted PostgreSQL-epoch timestamp
before converting it back to the Unix epoch? That way, we don't need
to worry about the overflow.

Let's merge these two if statements as they use the same error message.

Thanks for the review. Attached v4 addressing your feedback:

- 0002 now pre-computes the valid timestamp range in PostgreSQL-epoch
units (UUIDV7_MIN_TIMESTAMP / UUIDV7_MAX_TIMESTAMP) and validates
the shifted TimestampTz directly, before converting to Unix epoch.
This eliminates the overflow concern entirely.

- The pre-epoch and upper-bound checks are merged into a single if
statement.

0001 (infinite intervals) is unchanged from v3.

Thanks,
Baji Shaik

v4 looks good to me, I only have one completely optional nitpick comment:

+-- uuidv7: large future intervals that overflow epoch conversion are rejected
+SELECT uuidv7('292230 years'::interval);
+

There's no overflow anymore with the current patch, so that comment is somewhat stale. Maybe it could be simply "large future intervals are rejected"?

On Fri, Jul 3, 2026 at 2:54 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

There's no overflow anymore with the current patch, so that comment is
somewhat stale. Maybe it could be simply "large future intervals are
rejected"?

Thanks Zsolt! Good catch. Updated the test comment in v5 attached.

Thanks,
Baji Shaik