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
sergeyprokhore...@yahoo.com.au
On Thursday, 18 January 2024 at 09:31:16 pm GMT+3, Andrey Borodin
<x4...@yandex-team.ru> wrote:
> On 18 Jan 2024, at 20:39, Andrey Borodin <x4...@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.
