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

On 9/23/19 1:32 AM, Paul McGarry wrote:
> 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.

This has come up before and the general suggestion has been to have a
column for a naive(timestamp w/o tz) timestamp and a column for the
timezone. You are on the way there, only need to change the type of
'datetime' field.

>
> 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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com