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