Re: How to get timezone offset in timestamp with time zone AT TIMEZONE 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.klaver@aklaver.com