From: | Troy Frericks <troy(dot)frericks(at)iseatz(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | A bug with the TimeStampTZ data type and the 'AT TIME ZONE' clause |
Date: | 2022-02-11 23:30:40 |
Message-ID: | CAM=TFBw2j9crft14cz-mpLKvsM3Q7Vx7hASR9x==K3UJB8v9_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
-- A bug with the TimeStampTZ data type and the 'AT TIME ZONE' clause
--
-- I'm in the CST time zone.
-- A friend in the EST zone wants me to call him at 11:00 EST. I insert
that row.
-- To find the time I should call him, I select the row back for my time
zone, expecting 10:00 CST as: 11:00 EST = 10:00 CST... google: 11:00 EST to
CST
-- Troy Frericks, 07-Feb-2022
*SELECT* *version*() -- Get my Version, returns 14.0
*SELECT* *abbrev* -- Get my time zone, returns CST
*FROM* pg_timezone_names
*WHERE* *name* = *current_setting*('TIMEZONE');
-- DROP TABLE tsTable;
*CREATE* *TABLE* tsTable (tsCol *timestamptz*);
*INSERT* *INTO* tsTable *VALUES* ('2022-02-07 11:00:00' *AT* *TIME* *ZONE*
'EST'); -- 11:00 AM EST
*SELECT*
tsCol *AT* *time* *ZONE* 'CST' *AS* "CST" -- Query time as CST, Returns
2022-02-07 12:00:00.000, should be 10:00
*FROM* tsTable;
-- ###
--
CONFIDENTIALITY NOTICE:
This message and any attached files from iSeatz,
Inc. contain information that is confidential and proprietary under
applicable agreements and/or law. The recipient of this message is hereby
placed on notice that the information and materials transmitted herein by
iSeatz, Inc. are deemed to be the confidential information of iSeatz, Inc.
for all purposes. If you are not the intended recipient (or authorized to
receive for the recipient), you are hereby notified that any use,
dissemination, distribution, disclosure, or copying of this communication
or any information contained in the attachments hereto is strictly
prohibited. If you received this email by accident, please notify the
sender immediately and destroy this email and all copies of it. We may scan
and or monitor emails sent to and from our servers to ensure compliance to
protect our clients and business.
From | Date | Subject | |
---|---|---|---|
Next Message | Leon Timmermans | 2022-02-12 02:17:16 | Postgresql datetimes are not ISO-8601 compliant, but RFC3339 |
Previous Message | Tom Lane | 2022-02-11 21:54:54 | Re: BUG #17391: While using --with-ssl=openssl and PG_TEST_EXTRA='ssl' options, SSL tests fail on OpenBSD 7.0 |