Re: UUID v7

Hi Andrey,

Aleksander Alekseev wrote: "If this is the case, I think the example is indeed wrong". 

This is one of the reasons why I was categorically against any examples of implementation in the new RFC. The examples have been very poorly studied and discussed, and therefore it is better not to use them at all. But the text of the RFC itself clearly refers to UTC, and not at all about local time: "UUID version 7 features a time-ordered value field derived from the widely implemented and well known Unix Epoch timestamp source, the number of milliseconds since midnight 1 Jan 1970 UTC, leap seconds excluded". The main reason for using UTC is so that UUIDv7's, generated approximately simultaneously in different time zones, are correctly ordered in time when they get into one database.

Sergey Prokhorenko
sergeyprokhorenko(at)yahoo(dot)com(dot)au

On Thursday, 18 January 2024 at 07:22:05 pm GMT+3, Aleksander Alekseev <aleksander(at)timescale(dot)com> wrote:

Hi Andrey,

> > Timestamp and TimestampTz are absolutely the same thing.
> My question is not about Postgres data types. I'm asking about examples in the standard.
>
> There's an example 017F22E2-79B0-7CC3-98C4-DC0C0C07398F. It is expected to be generated on "Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00".
> It's exaplained to be 164555774200000ns after 1582-10-15 00:00:00 UTC.
>
> But 164555774200000ns after 1582-10-15 00:00:00 UTC  was  2022-02-22 19:22:22 UTC. And that was 2022-02-23 00:22:22 in UTC-05.

Not 100% sure which text you are referring to exactly, but I'm
guessing it's section B.2 of [1]

"""
This example UUIDv7 test vector utilizes a well-known 32 bit Unix
epoch with additional millisecond precision to fill the first 48 bits
[...]
The timestamp is Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00
represented as 0x17F22E279B0 or 1645557742000
"""

If this is the case, I think the example is indeed wrong:

```
=# select extract(epoch from 'Tuesday, February 22, 2022 2:22:22.00 PM
GMT-05:00' :: timestamptz)*1000;
      ?column?
----------------------
1645521742000.000000
(1 row)
```

And the difference between the value in the text and the actual value
is 10 hours as you pointed out.

Also you named the date 1582-10-15 00:00:00 UTC. Maybe you actually
meant 1970-01-01 00:00:00 UTC?

[1]: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html

--
Best regards,
Aleksander Alekseev