Re: Returning timestamp with timezone at specified timezone irrespective of client timezone

At 2020-09-27T18:31:49-04:00, Adrian Klaver <adrian.klaver@aklaver.com> sent:
> On 9/27/20 2:16 PM, aNullValue (Drew Stemen) wrote:
> > Hello,
> >
> > I've attempted to obtain help with this problem from several other
> > places, but numerous individuals recommended I ask this mailing list.
> >
> > What I need is for the ability to return a timestamp with timezone,
> > using the UTC offset that corresponds to a column-defined timezone,
> > irrespective of the client/session configured timezone.
> >
> > I have three columns in a table:
> > Timezone: 'US/Eastern'
> > Date: 2020-10-31
> > Time: 08:00
> >
> > The output I'm able to find includes these possibilities:
> > '2020-10-31 08:00:00'
> > '2020-10-31 12:00:00+00'
> >
> > Whereas what I actually need is:
> > '2020-10-31 08:00:00-05'
> >
> > Using the postgresql session-level timezone configuration won't work
> > because I need multiple timezones to be handled in a single set.
> >
> > Example code follows. I'm not using to_char in the examples as I likely
> > would in the production code, but I haven't found any way that it could
> > be helpful here regardless.
> >
> > ---------------------------------------------------
> >
> > SET TIME ZONE 'UTC';
> >
> > CREATE TABLE loc
> > (
> >      id serial not null,
> > timezone text not null,
> >      loc_date date NOT NULL,
> >      loc_time text NOT NULL,
> >      CONSTRAINT loc_pkey PRIMARY KEY (id),
> >      CONSTRAINT loc_loc_time_check CHECK (loc_time ~
> > '(^(2[0-3]|[01][0-9]|[0-9]):?([0-5][0-9]):?([0-5][0-9])?$)|(^(1[012]|0[1-9]|[1-9]):[0-5][0-9]
> > [AaPp][Mm]$)'::text)
> > )
> > ;
> >
> > INSERT INTO loc (timezone, loc_date, loc_time) VALUES
> > ('US/Eastern', '2020-10-31', '08:00'),
> > ('US/Eastern', '2020-11-03', '08:00'),
> > ('US/Central', '2020-10-31', '08:00'),
> > ('US/Central', '2020-11-03', '08:00');
> >
> > SELECT *
> > , timezone(l.timezone, l.loc_date + l.loc_time::time without time zone)
> > tswtz
> > , (l.loc_date + l.loc_time::time without time zone) tswotz
> > FROM loc l
> > ORDER BY timezone, loc_date, loc_time
> > ;
> >
> > ---------------------------------------------------
> >
> > id |  timezone  |  loc_date  | loc_time |         tswtz          |
> > tswotz
> > ----+------------+------------+----------+------------------------+---------------------
> >    7 | US/Central | 2020-10-31 | 08:00    | 2020-10-31 13:00:00+00 |
> > 2020-10-31 08:00:00
> >    8 | US/Central | 2020-11-03 | 08:00    | 2020-11-03 14:00:00+00 |
> > 2020-11-03 08:00:00
> >    5 | US/Eastern | 2020-10-31 | 08:00    | 2020-10-31 12:00:00+00 |
> > 2020-10-31 08:00:00
> >    6 | US/Eastern | 2020-11-03 | 08:00    | 2020-11-03 13:00:00+00 |
> > 2020-11-03 08:00:00
> > (4 rows)
> >
> > What I actually need is, in example id=7, '2020-10-31 08:00:00-05'.
> >
> > Is this even possible? Several people have proposed that I write a
> > custom function to do this on a per-row basis, which... I suppose I can
> > do... I'm just blown away that this isn't something that just works "out
> > of the box".
> >
>
> Something like?:
>
> select '2020-10-31' || ' 08:00 ' || utc_offset from pg_timezone_names
> where name = 'US/Eastern';
>            ?column?
> ----------------------------
>   2020-10-31 08:00 -04:00:00
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

The problem there is that the value of utc_offset in pg_timezone_names is correct only as of the current point in time,
andnot as of the date/time values in the row.