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

On 9/27/20 16:42, Jeremy Schneider wrote:
> On 9/27/20 16:13, Ron wrote:
>> On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:
>>> 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.
>>
>> Are you really asking what the TZ offset was on a specific date (Like
>> DST or not)?
>
> in lieu of having built-in support, a PL/pgSQL function to set the
> session-level timezone in between processing each record is the best
> approach i've thought of so far
FYI, here's the hack approach I was thinking of.

I intentionally didn't preserve the session's timezone in the
transaction, but that could easily be done with a few more lines of
PL/pgSQL.

create or replace function to_char(
v_tstz timestamp with time zone
,v_format text
,v_tz text
) returns text language plpgsql
immutable parallel safe
as $$
begin
perform set_config('timezone',v_tz,true);
return to_char(v_tstz,v_format);
end;
$$
;

SELECT
id
,to_char(l.loc_date+l.loc_time::time
,'YYYY-MM-DD HH24:MI:SSOF'
,timezone) tsw
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

id | tsw
----+------------------------
3 | 2020-10-31 03:00:00-05
4 | 2020-11-03 08:00:00-06
1 | 2020-10-31 09:00:00-04
2 | 2020-11-03 08:00:00-05

https://gist.github.com/aNullValue/ba838d6b40495695df0daa11c2748248

--
http://about.me/jeremy_schneider