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

[Paul McGarry <paul@xxxxxxxxxxxxxxx>]

Hi there,

Does anyone have a good way of doing:

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

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

=====

but with the output including the offset, eg:

2020-04-05 02:00:00+11

2020-04-05 02:00:00+10
respectively, so it is clear which 2am it is (the times above are around a DST switch)?


I have seen a couple of suggestions involving setting the desired time zone in the db session environment, but my actual use case will be a bit more complex, something like,

====
CREATE TABLE users (

user_id biginit, 
user_timezone text, -- Eg 'Australia/Sydney','Asia/Hong_Kong'
);
CREATE TABLE data (
id bigint,

user_id bigint,

datetime timestamp with time zone,

);

INSERT INTO users (1,'Australia/Sydney');
INSERT INTO users (2,'Asia/Hong_Kong');

INSERT INTO data (5,1,'2020-04-05 02:00:00');
INSERT INTO data (6,2,'2020-04-05 02:00:00');

====
and I'll want to run a query like:
====
select id, datetime,
  datetime AT TIME ZONE (select user_timezone from users where data.user_id=users.user_id) as usertime from data;
====

where I want the usertime to be returned in the corresponding users timezone, but with the offset. Therefore whatever renders the offset needs to be capable of doing it per row, independently of the server/session time zone.

And to_char isn't much help:

====
select to_char('2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney','YYYY-MM-DD HH24:MI:SSOF');
        to_char        
------------------------
 2020-04-05 02:00:00+00
====
 because to_char only deals with a timestamp and loses the timezone info and you end up with something very wrong.

Any ideas?

Thanks for any help.

Paul