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
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 |