Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

>>>>> "Paul" == Paul McGarry <paul(at)paulmcgarry(dot)com> writes:

Paul> Hi there,
Paul> Does anyone have a good way of doing:

Paul> =====
Paul> select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE
Paul> 'Australia/Sydney';
Paul> timezone
Paul> ---------------------
Paul> 2020-04-05 02:00:00

Paul> select '2020-04-04 16:00:00+00'::timestamp with time zone AT TIME ZONE
Paul> 'Australia/Sydney';
Paul> timezone
Paul> ---------------------
Paul> 2020-04-05 02:00:00
Paul> =====

Paul> but with the output including the offset, eg:
Paul> 2020-04-05 02:00:00+11
Paul> 2020-04-05 02:00:00+10

This is ugly in some respects but minimizes the possible hazards (since
using a function-local SET clause ensures that the timezone is reset on
exit):

create function ts_to_char(t timestamptz, z text) returns text
language plpgsql immutable
set timezone = 'GMT'
as $$
begin
perform set_config('timezone', z, true);
return t::text;
end;
$$;

select ts_to_char(timestamptz '2020-04-04 16:00:00+00', 'Australia/Sydney');
ts_to_char
------------------------
2020-04-05 02:00:00+10

--
Andrew (irc:RhodiumToad)