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 1pWXtw-0007BZ-Bz for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 07:26:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWXtv-000838-0x for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 07:26:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWXtu-0007vt-Fb for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 07:26:22 +0000 Received: from zcsmtaf02-pub.meteo.fr ([137.129.63.6]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWXtq-0001gY-S9 for pgsql-general@postgresql.org; Mon, 27 Feb 2023 07:26:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id C1D99427318F for ; Mon, 27 Feb 2023 07:26:15 +0000 (GMT) Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id wdf7y8w2b-z2 for ; Mon, 27 Feb 2023 07:26:15 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id A950542A96B5 for ; Mon, 27 Feb 2023 07:26:15 +0000 (GMT) X-Virus-Scanned: amavisd-new at meteo.fr Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id Nb5AdBVQ3O8S for ; Mon, 27 Feb 2023 07:26:15 +0000 (GMT) Received: from zcsmsm04.meteo.fr (zcsmsm04.meteo.fr [172.24.3.124]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id 969CD427318F for ; Mon, 27 Feb 2023 07:26:15 +0000 (GMT) Date: Mon, 27 Feb 2023 07:26:15 +0000 (GMT) From: PALAYRET Jacques To: pgsql-general@postgresql.org Message-ID: <1200461876.22810256.1677482775363.JavaMail.zimbra@meteo.fr> Subject: Interval in hours but not in days Leap second not taken into account MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_870c4c66-b5dd-4f80-83a7-ba46293fddd9" X-Originating-IP: [172.24.2.157] X-Mailer: Zimbra 9.0.0_GA_4485 (ZimbraWebClient - FF102 (Win)/9.0.0_GA_4478) Thread-Index: g28Y4bCNKV926+B77Lpoj1Ak7dG55w== Thread-Topic: Interval in hours but not in days Leap second not taken into account List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_870c4c66-b5dd-4f80-83a7-ba46293fddd9 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello,=20 # An interval in " years months ... seconds " given in seconds by EXTRACT(E= POCH ...) transtyped into INTERVAL :=20 SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12 = seconds'::interval) ) || ' seconds')::interval ;=20 interval=20 -------------=20 27772:11:12=20 # The same interval in seconds formated with TO_CHAR() :=20 SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 min= utes 12 seconds'::interval) ) || ' seconds')::interval, ' yyyy mm dd_ hh24-= mi-ss ') ;=20 to_char=20 ---------------------------=20 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 not = constant (leap year or not; a month can contain 31 30 ... days).=20 I thought that days were eliminated because of the leap second (extra secon= ds inserted in the UTC time scale); obviously, this is not the case.=20 # PostgreSQL does not take into account the additional second (leap second)= in some calendar days ; eg. 2016, 31 dec. :=20 SELECT to_timestamp('20170102 10:11:12','yyyymmdd hh24:mi:ss') - to_timesta= mp('20161230 00:00:00','yyyymmdd hh24:mi:ss') intervalle ;=20 intervalle=20 -----------------=20 3 days 10:11:12=20 --> With postgreSQL, a calendar day is always 86,400 seconds long.=20 So, is there a reason for this (interval in hours ...) ?=20 Regards=20 ----- M=C3=A9t=C3=A9o-France -----=20 PALAYRET Jacques=20 DCSC/GDC=20 jacques.palayret@meteo.fr=20 Fixe : +33 561078319=20 --=_870c4c66-b5dd-4f80-83a7-ba46293fddd9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello,

# An interval in "= years months ... seconds " given in seconds by EXTRACT(EPOCH ...) transtyp= ed into INTERVAL :
SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 1= 0 hours 11 minutes 12 seconds'::interval) ) || ' seconds')::interval ;
&= nbsp; interval
-------------
 27772:11:12

# The same inte= rval in seconds formated with TO_CHAR() :
SELECT TO_CHAR((EXTRACT= (EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12 seconds'::inter= val) ) || ' seconds')::interval, ' yyyy mm dd_ hh24-mi-ss ') ;
&n= bsp;         to_char
-----------= ----------------
  0000 00 00_ 27754-11-12

=3D> The resul= t is given in hours ... (not in days ...).

It is logical that there = are neither years nor months because they are not constant (leap year or no= t; a month can contain 31 30 ... days).
I thought that days were = eliminated because of the leap second (extra seconds inserted in the UTC ti= me scale); obviously, this is not the case.

# PostgreSQL does not ta= ke into account the additional second (leap second) in some calendar days ;= eg. 2016, 31 dec. :
SELECT to_timestamp('20170102 10:11:12','yyyymmdd h= h24:mi:ss') - to_timestamp('20161230 00:00:00','yyyymmdd hh24:mi:ss') inter= valle ;
   intervalle
-----------------
 3 days 10:= 11:12
--> With postgreSQL, a calendar day is always 86,400 seconds lo= ng.

So, is there a reason for this (interval in hours ...) ?

= Regards
----- M=C3=A9t=C3=A9o-France = -----
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe&= nbsp;: +33 561078319
--=_870c4c66-b5dd-4f80-83a7-ba46293fddd9--