Postgres Sql - How to apply Offset on Timestamp-CodePudding

My offset-date-time object I store in the DB with 2 columns, one timestamp(UTC) column and another corresponding offset.

For example, if I get: 2017-05-01T16:16:35-05:00, in the DB I will store this data in 2 columns the first timestamp will have the value in UTC (2017-05-01T11:16:35), and the offset column will have the -5 timezone in minutes so -300 in minutes.

Now I need select this data from DB, but I need to apply offset and again get the data that was entered: 2017-05-01T16:16:35-05:00.

I can achieve this in Java by selecting both values and applying offset. But I want to do DB level?

CodePudding user response：

Sorry, I can't add comment because of low score. I just tried below script, hope it can help.

create table test (id int, col2 timestamp, col3 int);

insert into test values(1, '2017-05-01T11:16:35', -300);

select * from test;

select (col2 - col3 * INTERVAL '1 minute')::varchar(32) ||  (col3/60) :: varchar(20) ||':00' from test;

We can finally get below result: 2017-05-01 16:16:35-5:00

CodePudding user response：

For example, if I get: 2017-05-01T16:16:35-05:00, in the DB I will store this data in 2 columns the first timestamp will have the value in UTC (2017-05-01T11:16:35), and the offset column will have the -5 timezone in minutes so -300 in minutes.

https://www.postgresql.org/docs/current/datatype-datetime.html. see: UTC offset for PST (ISO 8601 extended format)
So 2017-05-01T16:16:35-05:00 is an timestamptz type value, therefore at utc timezone value should be 2017-05-01 21:16:35 00!

demo

create table test_timestamp(
    org text,
    tsz timestamptz,
    ts timestamp,
    offsettz interval
);

org as timestamp or timestamptz input text. First we assume that org text format ending with something like '1999-01-08 04:05:06-8:00', the pattern is last part is like [ /-]99:99. the last part refer to the offset time to the UTC.

tsz cast text to timestamptz
ts ignore timezone, only timestamp.
offsettz interval can be positive or negative. offsettz is the pattern [ /-]99:99 text cast to interval. Then create a trigger, the only input is org text, all other 3 column would be computed via trigger.

CREATE OR REPLACE FUNCTION supporttsz ()
    RETURNS TRIGGER
    AS $$
BEGIN
    NEW.tsz := (NEW.org)::timestamptz;
    NEW.ts := (NEW.org)::timestamp;
    IF SUBSTRING((
    RIGHT (trim(NEW.org), 5)), 1, 1) = '-' THEN
        NEW.offsettz := (
        RIGHT (trim(NEW.org),
            5))::interval;
    elsif SUBSTRING((
        RIGHT (trim(NEW.org), 5)), 1, 1) = ' ' THEN
        NEW.offsettz := (
        RIGHT (trim(NEW.org),
            5))::interval;
    elsif SUBSTRING((
        RIGHT (trim(NEW.org), 6)), 1, 1) = '-' THEN
        NEW.offsettz := (
        RIGHT (trim(NEW.org),
            6))::interval;
    elsif SUBSTRING((
        RIGHT (trim(NEW.org), 6)), 1, 1) = ' ' THEN
        NEW.offsettz := (
        RIGHT (trim(NEW.org),
            6))::interval;
    ELSE
        NEW.offsettz := '0::00'::interval;
    END IF;
    RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tg_supporttsz_test_timestamp
    BEFORE INSERT ON test_timestamp FOR EACH ROW
    EXECUTE PROCEDURE supporttsz ();