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.palayret@meteo.fr
Fixe : +33 561078319