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

At 2020-09-27T19:36:34-04:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> sent:
> "aNullValue (Drew Stemen)" <drew(at)anullvalue(dot)net> writes:
> > 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 might be confused, but I think that the way to get the timestamptz
> values you want is
>
> # SELECT *
> , ((l.loc_date || ' ' || l.loc_time)::timestamp) at time zone timezone tswtz
> FROM loc l
> ORDER BY timezone, loc_date, loc_time
> ;
> id | timezone | loc_date | loc_time | tswtz
> ----+------------+------------+----------+------------------------
> 3 | US/Central | 2020-10-31 | 08:00 | 2020-10-31 13:00:00+00
> 4 | US/Central | 2020-11-03 | 08:00 | 2020-11-03 14:00:00+00
> 1 | US/Eastern | 2020-10-31 | 08:00 | 2020-10-31 12:00:00+00
> 2 | US/Eastern | 2020-11-03 | 08:00 | 2020-11-03 13:00:00+00
> (4 rows)
>
> These are the correct timestamptz values, as displayed with
> the session timezone set to UTC as per your example. If what
> you're asking for is that the *presentation* vary per the timezone
> column, then you have to fake it, because timestamptz_out simply
> will not do that for you. However, it's not very clear to me
> why you don't just concatenate the loc_date, loc_time, and timezone
> columns if that's the presentation you want.
>
> Alternatively, if this was just a dummy example and you really
> mean you've done a timestamptz calculation and now want to present
> it in a varying timezone, you could do something like this,
> using now() as a placeholder for some timestamptz expression:
>
> # select timezone, now(), (now() at time zone timezone) || ' ' ||
> timezone tswtz from loc l;
> timezone | now | tswtz
>
> ------------+-------------------------------+---------------------------------------
> US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27
> 19:32:19.321202 US/Eastern
> US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27
> 19:32:19.321202 US/Eastern
> US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27
> 18:32:19.321202 US/Central
> US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27
> 18:32:19.321202 US/Central
> (4 rows)
>
> The key thing to understand here is that AT TIME ZONE either
> rotates from local time to UTC, or vice versa, depending on
> whether its input is timestamp or timestamptz.
>
> regards, tom lane
>

I should have been much more clear about this ultimately being converted to text for output. I didn't simply because the default-rendering of the timestamptz column provided format identical to the character format I'll ultimately be using. Apologies for my lack of clarity.

I cannot simply append because then I'd wind up with your example, where 'US/Eastern' is appended to the computed string; I need that to be the actual UTC offset ('-04:00:00' or '-05:00:00', rather than the string 'US/Eastern'). The desired result is '2020-11-03 18:12:34-05:00:00', where '-05:00:00' is the effective UTC offset in the timezone specified by its name in another column.