"aNullValue (Drew Stemen)" <drew@xxxxxxxxxxxxxx> 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
