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

[Andrew Gierth <andrew@xxxxxxxxxxxxxxxxxxxx>]

>>>>> "Paul" == Paul McGarry <paul@xxxxxxxxxxxxxxx> 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)