Postgres Sql - How to apply Offset on Timestamp

Question

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?

Answer 1

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

Answer 2

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 at time zone 'utc';
    NEW.ts := (NEW.org)::timestamptz at time zone 'utc';
    NEW.ts := NEW.ts::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 ();