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?
Sorry if I left something ambiguous.
I should have written:
====
INSERT INTO data (5,1,'2020-04-05 02:00:00+00');
INSERT INTO data (6,2,'2020-04-05 02:00:00+00');
====
for clarity rather than leaving the offset ambiguous.
In terms of the general problem I don't think specific server timezone should really matter (though it is UTC in my case, it could feasibly be anything), what I am trying to do is output the stored time in both:
1) server time zone
2) user time zone
including displaying the relevant UTC offset in both cases.
(1) just comes for free, but it's getting the datetime and offset for (2) that is the question.
eg:
=====
SELECT
id,
datetime,
datetime AT TIME ZONE (SELECT user_timezone FROM users WHERE data.user_id=users.user_id) AS usertime
FROM data;
=====
and getting data something like:
id: 5
datetime: 2020-04-05 02:00:00+00
usertime: 2020-04-05 13:00:00+11
id: 6
datetime: 2020-04-05 02:00:00+00
usertime: 2020-04-05 10:00:00+08
(note above was done in my head as an example, I didn't check the real tz offsets at that point in time for those zones).
Andrew's function seems plausible and I need to find some time to test it, but I am slightly surprised there isn't a native way to get the output, as it seems like something that would be fairly useful when dealing with dates.
Perhaps another avenue would be some sort of getOffset function, eg
getOffset('2019-09-25 02:00:00+00','Australia/Sydney')
that would return +11 (or just 11).
Presumably PostgreSQL must have some internal functionality like that because it can do that math on the datetimes, but it doesn't seem to be exposed to users.
Thanks all for your input.
Paul