Thread: Interval in hours but not in days Leap second not taken into account
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).
---------------------------
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
# 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
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
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 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 ...) ?
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
Re: Interval in hours but not in days Leap second not taken into account
From
PALAYRET Jacques
Date:
Does PostgreSQL take into account daylight saving time in its calendar?
For the last summer hour of the spring (Daylight Saving Time), on Sunday March 27, 2022:
SELECT to_timestamp('20220329 00:00:00','yyyymmdd hh24:mi:ss') - to_timestamp('20220320 00:00:00','yyyymmdd
hh24:mi:ss')intervalle ;
intervalle
------------
9 days
Regards
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>, pgsql-general@postgresql.org
Envoyé: Lundi 27 Février 2023 09:23:37
Objet: Re: Interval in hours but not in days Leap second not taken into account
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 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 ...) ?
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
Re: Interval in hours but not in days Leap second not taken into account
From
PALAYRET Jacques
Date:
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='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" <jacques.palayret@meteo.fr>
À: "Laurenz Albe" <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Envoyé: Lundi 27 Février 2023 09:50:02
Objet: Re: Interval in hours but not in days Leap second not taken into account
Does PostgreSQL take into account daylight saving time in its calendar?
For the last summer hour of the spring (Daylight Saving Time), on Sunday March 27, 2022:
SELECT to_timestamp('20220329 00:00:00','yyyymmdd hh24:mi:ss') - to_timestamp('20220320 00:00:00','yyyymmdd
hh24:mi:ss')intervalle ;
intervalle
------------
9 days
Regards
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>, pgsql-general@postgresql.org
Envoyé: Lundi 27 Février 2023 09:23:37
Objet: Re: Interval in hours but not in days Leap second not taken into account
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 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 ...) ?
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
On Mon, Feb 27, 2023 at 8:26 PM PALAYRET Jacques
<jacques.palayret@meteo.fr> wrote:
> # 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
Bonjour Jacques,
Just for fun:
postgres=# SELECT utc_to_tai(to_timestamp('20170102
10:11:12','yyyymmdd hh24:mi:ss')) -
utc_to_tai(to_timestamp('20161230 00:00:00','yyyymmdd
hh24:mi:ss')) intervalle;
intervalle
-----------------
3 days 10:11:13
(1 row)
PostgreSQL could, in theory, provide built-in UTC/TAI conversions
functions using a leap second table that would be updated in each
minor release, considering that the leap second table is included in
the tzdata package that PostgreSQL vendors (ie includes a copy of),
but it doesn't do anything like that or know anything about leap
seconds. Here's a quick and dirty low technology version of the
above:
CREATE TABLE leap_seconds (time timestamptz primary key, off int);
-- refresh leap second table from ietf.org using not-very-secure hairy
shell code
BEGIN;
CREATE TEMP TABLE import_leap_seconds (s int8, off int);
COPY import_leap_seconds FROM PROGRAM 'curl -s
https://www.ietf.org/timezones/data/leap-seconds.list | grep -v ''^#''
| cut -f1,2';
TRUNCATE TABLE leap_seconds;
INSERT INTO leap_seconds (time, off)
SELECT '1900-01-01 00:00:00Z'::timestamptz + interval '1 second' * s, off
FROM import_leap_seconds;
DROP TABLE import_leap_seconds;
COMMIT;
CREATE OR REPLACE FUNCTION leap_seconds_before_utc_time(t timestamptz)
RETURNS int STRICT LANGUAGE SQL AS
$$
SELECT off FROM leap_seconds WHERE time <= t ORDER BY time DESC
FETCH FIRST ROW ONLY
$$;
CREATE OR REPLACE FUNCTION utc_to_tai(t timestamptz)
RETURNS timestamptz STRICT LANGUAGE SQL AS
$$
SELECT t + interval '1 second' * coalesce(leap_seconds_before_utc_time(t), 0);
$$;
CREATE OR REPLACE FUNCTION tai_to_utc(t timestamptz)
RETURNS timestamptz STRICT LANGUAGE SQL AS
$$
SELECT t - interval '1 second' * coalesce(leap_seconds_before_utc_time(t), 0);
$$;