Subject: Re: UUID v7
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Jelte Fennema-Nio <postgres@jeltef.nl>,
 Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>,
 Aleksander Alekseev <aleksander@timescale.com>,
 pgsql-hackers mailing list <pgsql-hackers@postgresql.org>,
 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>
References: 
 <CAAhFRxitJv=yoGnXUgeLB_O+M7J2BJAmb5jqAT9gZ3bij3uLDA@mail.gmail.com>
 <CAAhFRxj6Guh+_u+H7L_=VAJ5aeFTfH6Brb4OtCqu04xVHj29dg@mail.gmail.com>
 <6941A45B-C7D4-414B-A3A9-ACED172DFBD5@yandex-team.ru>
 <8DB43887-CA09-4845-83C0-33E8A79D0711@yandex-team.ru>
 <d9ac9cdc-8f2e-3794-f3b2-04f354e3b311@sztoch.pl>
 <CFE482C1-F001-4C51-89A3-084E7FF36157@yandex-team.ru>
 <CAGECzQQ=38bVUR=LZ6vmBCEjaDfOOoQa+ygFJ1mCG_H2jsC90Q@mail.gmail.com>
 <80E8F728-FC9C-461E-9B61-332E3AAEC066@yandex-team.ru>
 <CAJ7c6TPcYQHWYa97MiETT6O8=3Yq=r0CMtyu0y3DEYmOcmnmPA@mail.gmail.com>
 <2ECBCFF4-57A5-4484-B6D3-872596E40654@yandex-team.ru>
 <1199536369.1612345.1705423753699@mail.yahoo.com>
 <5BC4E87A-C6BA-47D6-B723-1F1AABEC09BC@yandex-team.ru>
 <CAGECzQS=EjfLxdX89N95tHFGXS4m1aj2V_+xrJppBohgaKQhtQ@mail.gmail.com>
 <e9f035db-b3ec-110d-326e-fc85e82ccf92@sztoch.pl>
 <CAGECzQTrHF_PDNyHTKwa0Et+D2MstbhtPfS25EQT6wqC1MrfZA@mail.gmail.com>
 <1ED19D0E-BDA6-4B1B-9722-2322B69CE151@yandex-team.ru>
From: =?UTF-8?Q?Przemys=c5=82aw_Sztoch?= <przemyslaw@sztoch.pl>
Message-ID: <4c8be215-cdf6-e9e5-f9bb-d0eeaa58ad9b@sztoch.pl>
Date: Thu, 18 Jan 2024 21:26:55 +0100
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.60
MIME-Version: 1.0
In-Reply-To: <1ED19D0E-BDA6-4B1B-9722-2322B69CE151@yandex-team.ru>
Content-Type: multipart/alternative;
 boundary="------------F6F08CB93CF1BC6F9EE9CF5E"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/4c8be215-cdf6-e9e5-f9bb-d0eeaa58ad9b%40sztoch.pl>
Precedence: bulk

This is a multi-part message in MIME format.
--------------F6F08CB93CF1BC6F9EE9CF5E
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Using localtime would be absurd. Especially since time goes back during 
summer time change.
I believe our implementation should use UTC. No one forbids us from 
assuming that our local time for generating uuid is UTC.

Andrey Borodin wrote on 1/18/2024 2:17 PM:
>
>> On 17 Jan 2024, at 02:19, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> I want to ask Kyzer or Brad, I hope they will see this message. I'm working on the patch for time extraction for v1, v6 and v7.
>
> Do I understand correctly, that UUIDs contain local time, not UTC time? For examples in [0] I see that "A.6. Example of a UUIDv7 Value" I see that February 22, 2022 2:22:22.00 PM GMT-05:00 results in unix_ts_ms = 0x017F22E279B0, which is not UTC, but local time.
> Is it intentional? Section "5.1. UUID Version 1" states otherwise.
>
> If so, I should swap signatures of functions from TimestampTz to Timestamp.
> I'm hard-coding examples from this standard to tests, so I want to be precise...
>
> If I follow the standard I see this in tests:
> +-- extract UUID v1, v6 and v7 timestamp
> +SELECT uuid_extract_time('C232AB00-9414-11EC-B3C8-9F6BDECED846') at time zone 'GMT-05';
> +         timezone
> +--------------------------
> + Wed Feb 23 00:22:22 2022
> +(1 row)
>
> Current patch version attached. I've addressed all other requests: function renames, aliases, multiple functions instead of optional params, cleaner catalog definitions, not throwing error when [var,ver,time] value is unknown.
> What is left: deal with timezones, improve documentation.
>
>
> Best regards, Andrey Borodin.
>
> [0] https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#name-example-of-a-uuidv1-value

-- 
Przemysław Sztoch | Mobile +48 509 99 00 66

--------------F6F08CB93CF1BC6F9EE9CF5E
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html theme="default-dark" iconset="color"><head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head><body text="#000000">Using localtime would be absurd. Especially 
since time goes back during summer time change.<br>

I believe our implementation should use UTC. No one forbids us from 
assuming that our local time for generating uuid is UTC.<br>
<br>
<span>Andrey Borodin wrote on 1/18/2024 2:17 PM:</span><br>
<blockquote type="cite" 
cite="mid:1ED19D0E-BDA6-4B1B-9722-2322B69CE151@yandex-team.ru">
  <pre wrap="">

</pre>
  <blockquote type="cite"><pre wrap="">On 17 Jan 2024, at 02:19, Jelte Fennema-Nio <a class="moz-txt-link-rfc2396E" href="mailto:postgres@jeltef.nl">&lt;postgres@jeltef.nl&gt;</a> wrote:
</pre></blockquote>
  <pre wrap="">
I want to ask Kyzer or Brad, I hope they will see this message. I'm working on the patch for time extraction for v1, v6 and v7.

Do I understand correctly, that UUIDs contain local time, not UTC time? For examples in [0] I see that "A.6. Example of a UUIDv7 Value" I see that February 22, 2022 2:22:22.00 PM GMT-05:00 results in unix_ts_ms = 0x017F22E279B0, which is not UTC, but local time.
Is it intentional? Section "5.1. UUID Version 1" states otherwise.

If so, I should swap signatures of functions from TimestampTz to Timestamp.
I'm hard-coding examples from this standard to tests, so I want to be precise...

If I follow the standard I see this in tests:
+-- extract UUID v1, v6 and v7 timestamp
+SELECT uuid_extract_time('C232AB00-9414-11EC-B3C8-9F6BDECED846') at time zone 'GMT-05';
+         timezone         
+--------------------------
+ Wed Feb 23 00:22:22 2022
+(1 row)

Current patch version attached. I've addressed all other requests: function renames, aliases, multiple functions instead of optional params, cleaner catalog definitions, not throwing error when [var,ver,time] value is unknown.
What is left: deal with timezones, improve documentation.


Best regards, Andrey Borodin.

[0] <a class="moz-txt-link-freetext" href="https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#name-example-of-a-uuidv1-value">https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#name-example-of-a-uuidv1-value</a>
</pre>
</blockquote>
<br>
<div class="moz-signature">-- <br><span style="font-weight: bold;">Przemysław
 Sztoch</span> | Mobile +48 509 99 00 66<br></div>
</body></html>

--------------F6F08CB93CF1BC6F9EE9CF5E--