Interval in hours but not in days Leap second not taken into account

From: PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Interval in hours but not in days Leap second not taken into account
Date: 2023-02-27 07:26:15
Message-ID: 1200461876.22810256.1677482775363.JavaMail.zimbra@meteo.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

# 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 12 seconds'::interval) ) || ' seconds')::interval ;
interval
-------------
27772:11:12

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

=> The result 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 not; a month can contain 31 30 ... days).
I thought that days were eliminated because of the leap second (extra seconds inserted in the UTC time scale); obviously, this is not the case.

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

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

Regards
----- Météo-France -----
PALAYRET Jacques
DCSC/GDC
jacques(dot)palayret(at)meteo(dot)fr
Fixe : +33 561078319

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2023-02-27 08:23:37 Re: Interval in hours but not in days Leap second not taken into account
Previous Message Jan Bilek 2023-02-27 06:28:37 ERROR: unsupported Unicode escape sequence - in JSON-type column