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?
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!
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.
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 ();