Re: Interval in hours but not in days Leap second not taken into account - Mailing list pgsql-general

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);
$$;