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

On 9/23/19 7:40 PM, Andrew Gierth wrote:
>>>>>> "Adrian" == Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>
> Adrian> This has come up before and the general suggestion has been to
> Adrian> have a column for a naive(timestamp w/o tz) timestamp and a
> Adrian> column for the timezone.
>
> No, that's usually (not always) backwards, and in any event wouldn't
> solve this particular issue.
>

The issue is unclear so I am not sure you can discount this as a
solution. The OP had:

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:

"Therefore whatever renders the offset needs to be capable of doing it
per row, independently of the server/session time zone."

There is no indication of what the server timezone is set to or where
the timestamps being assigned to date.datetime are coming from. Do they
originate as local time(per user) or are they being generated server side?

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