"aNullValue (Drew Stemen)" <drew@anullvalue.net> writes:
> What I need is for the ability to return a timestamp with timezone, using the UTC offset that corresponds to a
column-definedtimezone, 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