Horton

Re: UUID v7

Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>

View thread View on postgr.es
From: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>
To: Aleksander Alekseev <aleksander@timescale.com>, Andrey Borodin <x4mmm@yandex-team.ru>
Cc: pgsql-hackers mailing list <pgsql-hackers@postgresql.org>, Przemysław Sztoch <przemyslaw@sztoch.pl>, Jelte Fennema-Nio <postgres@jeltef.nl>, Nick Babadzhanian <pgnickb@gmail.com>, Mat Arye <mat@timescaledb.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, Daniel Gustafsson <daniel@yesql.se>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Nikolay Samokhvalov <samokhvalov@gmail.com>, "Kyzer Davis (kydavis)" <kydavis@cisco.com>, Andres Freund <andres@anarazel.de>, "brad@peabody.io" <brad@peabody.io>, Kirk Wolak <wolakk@gmail.com>
Date: 2024-01-18T18:59:38Z
Lists: pgsql-hackers 
Hi Andrey,

You'd better generate a test UUIDv7 for midnight 1 Jan 1970 UTC. In this case, the timestamp in UUIDv7 according to the new RFC must be filled with zeros. By extracting the timestamp from this test UUIDv7, you should get exactly midnight 1 Jan 1970 UTC.
I also recommend this article: https://habr.com/ru/articles/772954/


Sergey Prokhorenko
sergeyprokhorenko@yahoo.com.au 

    On Thursday, 18 January 2024 at 09:31:16 pm GMT+3, Andrey Borodin <x4mmm@yandex-team.ru> wrote:  
 
 

> On 18 Jan 2024, at 20:39, Andrey Borodin <x4mmm@yandex-team.ru> wrote:
> 
> 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.


'2022-02-22 19:22:22 UTC' is exactly that moment which was encoded into example UUIDs. It's not '2022-02-23 00:22:22 in UTC-05' as I thought.
I got confused by "at timezone" changes which in fact removes timezone information. And that's per SQL standard...

Now I'm completely lost in time... I've set local time to NY (UTC-5).

postgres=# select TIMESTAMP WITH TIME ZONE '2022-02-22 14:22:22-05' - TIMESTAMP WITH TIME ZONE 'Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00';
 ?column? 
----------
 10:00:00
(1 row)

postgres=# select TIMESTAMP WITH TIME ZONE 'Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00';
      timestamptz      
------------------------
 2022-02-22 04:22:22-05
(1 row)

I cannot wrap my mind around it... Any pointers would be appreciated.
I'm certain that code extracted UTC time correctly, I just want a reliable test that verifies timestamp constant (+ I understand what is going on).


Best regards, Andrey Borodin.

Commits

  1. Fix timestamp overflow in UUIDv7 implementation.

  2. Add UUID version 7 generation function.

  3. Add some UUID support functions