-- 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.
