Postgres - convert international atomic time to UTC time (for loop with IF inside SQL function body)

Question

I need to store TAI time in a pg database. This requires a custom type,

CREATE TYPE tai AS (
    secs int,
    nanosecs, int
);

which maps 1:1 to a GNU C timespec struct, with the TAI epoch of Jan 1 1958 00:00:00 and monotonic clock at its origins. A table of leapseconds is auxiliary data required to convert these to UTC timestamps,

DROP TABLE IF EXISTS leapseconds;
CREATE TABLE IF NOT EXISTS leapseconds (
    id serial PRIMARY KEY,
    moment TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    skew int NOT NULL
);
INSERT INTO leapseconds (moment, skew) VALUES -- note: pg assumes 00:00:00 if no hh:mm:ss given
    ('1972-Jan-01', 10),
    ('1972-Jun-30', 1),
    ('1972-Dec-31', 1),
    ('1973-Dec-31', 1),
    ('1974-Dec-31', 1),
    ('1975-Dec-31', 1),
    ('1976-Dec-31', 1),
    ('1977-Dec-31', 1),
    ('1978-Dec-31', 1),
    ('1979-Dec-31', 1),
    ('1981-Jun-30', 1),
    ('1982-Jun-30', 1),
    ('1983-Jun-30', 1),
    ('1985-Jun-30', 1),
    ('1987-Dec-31', 1),
    ('1989-Dec-31', 1),
    ('1990-Dec-31', 1),
    ('1992-Jun-30', 1),
    ('1993-Jun-30', 1),
    ('1994-Jun-30', 1),
    ('1995-Dec-31', 1),
    ('1997-Jun-30', 1),
    ('1998-Dec-31', 1),
    ('2005-Dec-31', 1),
    ('2008-Dec-31', 1),
    ('2012-Jun-30', 1),
    ('2015-Jun-30', 1),
    ('2016-Dec-31', 1)
;

I need a function to convert these to UTC timestamps. It would be optimal for for this to live in postgres to avoid latency. The SQL/python pseudocode to do this is

# SQL
SELECT (moment, skew) 
FROM LEAPSECONDS
ORDER BY MOMEN ASC
AS tuples

# python
def tai_to_utc(tai):
    modtime = to_timestamp(tai.seconds) # to_timestamp from pgsql
    modtime += tai.nanosec; # timestamp in pg has usec precision, gloss over it
    for moment, skew in tuples:
        if modtime > moment:
            modtime += skew # type mismatch, gloss over it

    return modtime

I know how to do the typecasting, but I'm struggling to write this for+if in plpsql. Is the path of least resistance to learn how to write a stored C procedure and do this in the database? I can also have the client provide the UTC timestamps and do this conversion based on a query to the database, but the chatter to pull data from the database in order to insert into it is going to really hurt ingest speed.

sticky bit · Answer 1 · 2021-08-08T06:21:25.853

You basically need to use the sum() window function to get the cumulative sum of leap seconds over the moments. Add that to the base timestamp (without the leap seconds) and get the one with the youngest moment where the moment is older or at the base timestamp with the leap seconds added for all the previous moments. You can use DISTINCT ON and LIMIT for that.

CREATE FUNCTION tai_to_utc
                (_tai tai)
                RETURNS timestamptz
AS
$$
SELECT DISTINCT ON (moment)
       ts
       FROM (SELECT moment AT TIME ZONE 'UTC' AS moment,
                    skew,
                    '1958-01-01T00:00:00+00:00'::timestamptz
                    + (_tai.secs || ' seconds')::interval
                    + (_tai.nanosecs / 1000 || ' microseconds')::interval
                    + (sum(skew) OVER (ORDER BY moment) || ' seconds')::interval AS ts
                    FROM (SELECT moment,
                                 skew
                                 FROM leapseconds
                          UNION ALL
                          SELECT '-infinity',
                                 0) AS x) AS y
       WHERE moment <= ts - (skew || ' seconds')::interval
       ORDER BY moment DESC
       LIMIT 1;
$$
LANGUAGE SQL;

I'd however recommend to also change the type of leapseconds.moment to timestamptz (timestamp with time zone) and insert the moments with explicit time zone to ensure things are what they meant to be. That way the awkward time zone conversion isn't needed in the function.

CREATE FUNCTION tai_to_utc
                (_tai tai)
                RETURNS timestamptz
AS
$$
SELECT DISTINCT ON (moment)
       ts
       FROM (SELECT moment,
                    skew,
                    '1958-01-01T00:00:00+00:00'::timestamptz
                    + (_tai.secs || ' seconds')::interval
                    + (_tai.nanosecs / 1000 || ' microseconds')::interval
                    + (sum(skew) OVER (ORDER BY moment) || ' seconds')::interval AS ts
                    FROM (SELECT moment,
                                 skew
                                 FROM leapseconds
                          UNION ALL
                          SELECT '-infinity',
                                 0) AS x) AS y
       WHERE moment <= ts - (skew || ' seconds')::interval
       ORDER BY moment DESC
       LIMIT 1;
$$
LANGUAGE SQL;

db<>fiddle

An index on leapseconds (moment, skew) might improve performance. Though leapseconds is quite very small, so it might not do that much.

Postgres - convert international atomic time to UTC time (for loop with IF inside SQL function body)

1 Answers1

Linked