Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWak3-0007pZ-Tw for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 10:28:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWak2-0005mz-Pl for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 10:28:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWak2-0005mG-EY for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 10:28:22 +0000 Received: from zcsmtaf01-pub.meteo.fr ([137.129.63.5]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWajz-0007GL-Dc for pgsql-general@postgresql.org; Mon, 27 Feb 2023 10:28:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf01-pub.meteo.fr (Postfix) with ESMTP id 34407419282D; Mon, 27 Feb 2023 10:28:18 +0000 (GMT) Received: from zcsmtaf01-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf01.meteo.fr [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id WAyu89tGKllV; Mon, 27 Feb 2023 10:28:18 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf01-pub.meteo.fr (Postfix) with ESMTP id 19CC6419283B; Mon, 27 Feb 2023 10:28:18 +0000 (GMT) X-Virus-Scanned: amavisd-new at meteo.fr Received: from zcsmtaf01-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf01.meteo.fr [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id km3M5T0iiAcc; Mon, 27 Feb 2023 10:28:18 +0000 (GMT) Received: from zcsmsm04.meteo.fr (zcsmsm04.meteo.fr [172.24.3.124]) by zcsmtaf01-pub.meteo.fr (Postfix) with ESMTP id 086F7419282D; Mon, 27 Feb 2023 10:28:18 +0000 (GMT) Date: Mon, 27 Feb 2023 10:28:17 +0000 (GMT) From: PALAYRET Jacques To: Laurenz Albe Cc: pgsql-general@postgresql.org Message-ID: <363231457.23083812.1677493697778.JavaMail.zimbra@meteo.fr> In-Reply-To: <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr> References: <1200461876.22810256.1677482775363.JavaMail.zimbra@meteo.fr> <96ad392589f3f147b0b5dd240f4903cd58eb5bfb.camel@cybertec.at> <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr> Subject: Re: Interval in hours but not in days Leap second not taken into account MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Originating-IP: [172.24.2.157] X-Mailer: Zimbra 9.0.0_GA_4485 (ZimbraWebClient - FF102 (Win)/9.0.0_GA_4478) Thread-Topic: Interval in hours but not in days Leap second not taken into account Thread-Index: qn69TdMD9WRxmmbQT1fIoj4pdjHWrY5cYCeP List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz Albe, you are right, thank you; actually, it depends (of course) on= the time zone: # With GMT (no Daylight Saving Time): SHOW timezone ; TimeZone ---------- GMT SELECT timestamp with time zone '2022-03-29 12:00:00' - timestamp with time= zone '2022-03-26 12:00:00' ; ?column? ---------- 3 days # With a time zone that IS dst (Daylight Saving Time): SET timezone=3D'Africa/Casablanca' ; SHOW timezone ; TimeZone ------------------- Africa/Casablanca SELECT timestamp with time zone '2022-03-29 12:00:00' - timestamp with time= zone '2022-03-26 12:00:00' ; ?column? ----------------- 3 days 01:00:00 Regards ----- Mail original ----- De: "PALAYRET Jacques" =C3=80: "Laurenz Albe" Cc: pgsql-general@postgresql.org Envoy=C3=A9: Lundi 27 F=C3=A9vrier 2023 09:50:02 Objet: Re: Interval in hours but not in days Leap second not taken into ac= count Does PostgreSQL take into account daylight saving time in its calendar? For the last summer hour of the spring (Daylight Saving Time), on Sunday Ma= rch 27, 2022: SELECT to_timestamp('20220329 00:00:00','yyyymmdd hh24:mi:ss') - to_timesta= mp('20220320 00:00:00','yyyymmdd hh24:mi:ss') intervalle ; intervalle ------------ 9 days Regards ----- Mail original ----- De: "Laurenz Albe" =C3=80: "PALAYRET Jacques" , pgsql-general@postg= resql.org Envoy=C3=A9: Lundi 27 F=C3=A9vrier 2023 09:23:37 Objet: Re: Interval in hours but not in days Leap second not taken into ac= count On Mon, 2023-02-27 at 07:26 +0000, PALAYRET Jacques wrote: > # An interval in " years months ... seconds " given in seconds by EXTRACT= (EPOCH ...) transtyped into INTERVAL : > SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 1= 2 seconds'::interval) ) || ' seconds')::interval ; > =C2=A0 interval > ------------- > =C2=A027772:11:12 >=20 > # The same interval in seconds formated with TO_CHAR() : > SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 m= inutes 12 seconds'::interval) ) || ' seconds')::interval, ' yyyy mm dd_ hh2= 4-mi-ss ') ; > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 to_char > --------------------------- > =C2=A0 0000 00 00_ 27754-11-12 >=20 > =3D> The result is given in hours ... (not in days ...). >=20 > It is logical that there are neither years nor months because they are no= t constant > (leap year or not; a month can contain 31 30 ... days). > I thought that days were eliminated because of the leap second (extra sec= onds > inserted in the UTC time scale); obviously, this is not the case. >=20 > # PostgreSQL does not take into account the additional second (leap secon= d) in some calendar days ; eg. 2016, 31 dec. : > SELECT to_timestamp('20170102 10:11:12','yyyymmdd hh24:mi:ss') - to_times= tamp('20161230 00:00:00','yyyymmdd hh24:mi:ss') intervalle ; > =C2=A0=C2=A0 intervalle > ----------------- > =C2=A03 days 10:11:12 > --> With postgreSQL, a calendar day is always 86,400 seconds long. >=20 > So, is there a reason for this (interval in hours ...) ? The best explanation I have is "daylight savings time". One day is not always 24 hours long. If you keep the interval in hours, the result is always correct (if you ignore leap seconds, which PostgreSQL doesn't account for). Yours, Laurenz Albe