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