Home > Software design >  Postgres Sql - How to apply Offset on Timestamp
Postgres Sql - How to apply Offset on Timestamp

Time:05-14

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 ();
  • Related