Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQZBH-0027x8-Dz for pgsql-hackers@arkaria.postgresql.org; Thu, 18 Jan 2024 20:40:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rQZBG-005Prv-3h for pgsql-hackers@arkaria.postgresql.org; Thu, 18 Jan 2024 20:40:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQZBF-005Prn-Li for pgsql-hackers@lists.postgresql.org; Thu, 18 Jan 2024 20:40:05 +0000 Received: from mail.ltc.pl ([194.24.180.24]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rQZBC-002D0k-6q for pgsql-hackers@postgresql.org; Thu, 18 Jan 2024 20:40:04 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.ltc.pl (Postfix) with ESMTP id 47E9ADF9CE; Thu, 18 Jan 2024 21:39:59 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sztoch.pl; s=mail; t=1705610399; bh=gPtFFF4XeN4mrvf95FTsEAn639NxZVdq4MeIuWTQJwg=; h=Subject:To:Cc:References:From:Date:In-Reply-To:From; b=fCW41g0cpD94Cx0SnKLGurHtf8lDNFXgK8+KnqLJ5JO0CDE1UgT44XtvwUDqQrw0y eGB/7z2MZH7A2IaPDaRKq6x+Y9Apc1i6bnhiRHg7/Xb1ysFR1+SXcSe6TrRy7RLX3C 5SiVSaWqdJP/cZqIFIVYHvXNeIc9tKvxS4FTJJC2+FAxxt7Lfkb6wvW/8oIhxoKUsy sY56p0s0l2fWPiTc3Tihug6zyqExW8o8Mi91g884FdU+RRl/+3S+XjYn1UrLFAPATY yOdAefc109RGE30qVT5iiplEEc1JFKO8yu4B9YM4bAi/CN8/pW05kLDbuOZrHiQr7M DuygvN+yswEEw== X-Virus-Scanned: Debian amavisd-new at mail.ltc.pl Received: from mail.ltc.pl ([127.0.0.1]) by localhost (mail.ltc.pl [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 55BIAtE8nsz7; Thu, 18 Jan 2024 21:39:42 +0100 (CET) Received: from [192.168.56.98] (fu182.internetdsl.tpnet.pl [80.53.46.182]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail.ltc.pl (Postfix) with ESMTPSA; Thu, 18 Jan 2024 21:39:42 +0100 (CET) Subject: Re: UUID v7 To: Aleksander Alekseev Cc: pgsql-hackers mailing list , Andrey Borodin , Sergey Prokhorenko , Jelte Fennema-Nio , Nick Babadzhanian , Mat Arye , Peter Eisentraut , Tom Lane , Daniel Gustafsson , Matthias van de Meent , Nikolay Samokhvalov , "Kyzer Davis (kydavis)" , Andres Freund , "brad@peabody.io" , Kirk Wolak References: <6941A45B-C7D4-414B-A3A9-ACED172DFBD5@yandex-team.ru> <8DB43887-CA09-4845-83C0-33E8A79D0711@yandex-team.ru> <1003561938.6914482.1704452267331@mail.yahoo.com> <80E8F728-FC9C-461E-9B61-332E3AAEC066@yandex-team.ru> <67eb8a02-f35c-af10-1615-afae0f9f7548@sztoch.pl> From: =?UTF-8?Q?Przemys=c5=82aw_Sztoch?= Message-ID: Date: Thu, 18 Jan 2024 21:39:41 +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: Content-Type: multipart/alternative; boundary="------------E4766B26B4E4E39695A110E4" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------E4766B26B4E4E39695A110E4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Aleksander Alekseev wrote on 1/18/2024 3:20 PM: > Hi, > >> Another question: how did you choose between using TimestampTz and >> Timestamp types? I realize that internally it's all the same. Maybe >> Timestamp will be slightly better since the way it is displayed >> doesn't depend on the session settings. Many people I talked to find >> this part of TimestampTz confusing. >> >> timstamptz internally always store UTC. >> I believe that in SQL, when operating with time in UTC, you should always use timestamptz. >> timestamp is theoretically the same thing. But internally it does not convert time to UTC and will lead to incorrect use. > No. > > Timestamp and TimestampTz are absolutely the same thing. The only > difference is how they are shown to the user. TimestampTz uses session > context in order to be displayed in the TZ chosen by the user. Thus > typically it is somewhat more confusing to the users and thus I asked > whether there was a good reason to choose TimestampTz over Timestamp. > Theoretically, you're right. But look at this example: SET timezone TO 'Europe/Warsaw'; SELECT extract(epoch from '2024-01-18 9:27:30'::timestamp), extract(epoch from '2024-01-18 9:27:30'::timestamptz);  date_part  | date_part ------------+------------  1705570050 | 1705566450 (1 row) In my opinion, timestamptz gives greater guarantees that the time internally is in UTC and the user gets the time in his/her time zone. In the case of timestamp, it is never certain whether it keeps time in UTC or in the local zone. In the case of argument's type, there would be no problem because we could create two functions. Of course timestamp would be treated the same as timestamptz. But here we have a problem with the function return type, which can only be one. And since the time returned is in UTC, it should be timestamptz. -- Przemysław Sztoch | Mobile +48 509 99 00 66 --------------E4766B26B4E4E39695A110E4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Aleksander Alekseev wrote on 1/18/2024 3:20 PM:
Hi,


Another question: how did you choose between using TimestampTz and
Timestamp types? I realize that internally it's all the same. Maybe
Timestamp will be slightly better since the way it is displayed
doesn't depend on the session settings. Many people I talked to find
this part of TimestampTz confusing.

timstamptz internally always store UTC.
I believe that in SQL, when operating with time in UTC, you should always use timestamptz.
timestamp is theoretically the same thing. But internally it does not convert time to UTC and will lead to incorrect use.

No.

Timestamp and TimestampTz are absolutely the same thing. The only
difference is how they are shown to the user. TimestampTz uses session
context in order to be displayed in the TZ chosen by the user. Thus
typically it is somewhat more confusing to the users and thus I asked
whether there was a good reason to choose TimestampTz over Timestamp.

Theoretically, you're right. But look at this example:

SET timezone TO 'Europe/Warsaw';
SELECT extract(epoch from '2024-01-18 9:27:30'::timestamp), extract(epoch from '2024-01-18 9:27:30'::timestamptz);

 date_part  | date_part
------------+------------
 1705570050 | 1705566450
(1 row)

In my opinion, timestamptz gives greater guarantees that the time internally is in UTC and the user gets the time in his/her time zone.

In the case of timestamp, it is never certain whether it keeps time in UTC or in the local zone.

In the case of argument's type, there would be no problem because we could create two functions.
Of course timestamp would be treated the same as timestamptz.
But here we have a problem with the function return type, which can only be one. And since the time returned is in UTC, it should be timestamptz.

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