Thread: A bug with the TimeStampTZ data type and the 'AT TIME ZONE' clause
-- 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.
Re: A bug with the TimeStampTZ data type and the 'AT TIME ZONE' clause
-- 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;
Troy Frericks <troy.frericks@iseatz.com> writes: > *CREATE* *TABLE* tsTable (tsCol *timestamptz*); > *INSERT* *INTO* tsTable *VALUES* ('2022-02-07 11:00:00' *AT* *TIME* *ZONE* > 'EST'); -- 11:00 AM EST All these asterisks make your example unreadable, not to mention very difficult to copy-and-paste. Please do not do that in future. Anyway, the problem here is that you are invoking the wrong one of the two AT TIME ZONE operators. There's one that takes timestamp and returns timestamptz, which is what you meant to use (or should have, anyway), but there's also one that takes timestamptz and returns timestamp. In the absence of any type decoration, the second one will win because timestamptz is a preferred type. So what you actually got out of that was (a) the undecorated literal was presumed to be of time timestamptz, and since it doesn't mention a UTC offset, was assumed to be in your timezone, so you got the equivalent of: regression=# show timezone; TimeZone ----------------- America/Chicago (1 row) regression=# select '2022-02-07 11:00:00'::timestamptz; timestamptz ------------------------ 2022-02-07 11:00:00-06 (1 row) (b) AT TIME ZONE rotates that to a timestamp-without-tz, expressed in the EST zone: regression=# select '2022-02-07 11:00:00'::timestamptz AT TIME ZONE 'EST'; timezone --------------------- 2022-02-07 12:00:00 (1 row) Note the lack of any zone indicator in the output --- that's the easiest way to tell whether a value is timestamp or timestamptz, though you could also try applying pg_typeof() to the expression. (c) INSERT casts that back to timestamptz, again assuming your prevailing zone, so what actually goes into the table is regression=# select ('2022-02-07 11:00:00'::timestamptz AT TIME ZONE 'EST')::timestamptz; timezone ------------------------ 2022-02-07 12:00:00-06 (1 row) If you'd cast the unmarked literal to timestamp explicitly, you'd have got the behavior you're after: regression=# select ('2022-02-07 11:00:00'::timestamp AT TIME ZONE 'EST')::timestamptz; timezone ------------------------ 2022-02-07 10:00:00-06 (1 row) > CONFIDENTIALITY NOTICE: > This message and any attached files from iSeatz, > Inc. contain information that is confidential and proprietary under > applicable agreements and/or law. You really need to point out to your corporate lawyers that this sort of thing is unenforceable, counterproductive, and silly-looking. You submitted to a publicly-archived mailing list. If I thought this notice actually meant anything, I've have been afraid to answer at all. regards, tom lane
DROP TABLE ts;
CREATE TABLE ts (ts timestamptz);
-- 10:00 AM CST is the same moment as 11:00 AM EST -- google: what is 10:00 AM CST in EST
INSERT INTO ts VALUES (('2022-02-07 10:00:00'::timestamp AT TIME ZONE 'America/Chicago')::timestamptz); -- 10:00 AM CT
INSERT INTO ts VALUES (('2022-02-07 11:00:00'::timestamp AT TIME ZONE 'America/New_York')::timestamptz); -- 11:00 AM ET
SELECT
ts AT time ZONE 'America/Chicago' AS "CT"
FROM ts;
|-----------------------|
|2022-02-07 10:00:00.000|
|2022-02-07 10:00:00.000|
Troy Frericks <troy.frericks@iseatz.com> writes:
> *CREATE* *TABLE* tsTable (tsCol *timestamptz*);
> *INSERT* *INTO* tsTable *VALUES* ('2022-02-07 11:00:00' *AT* *TIME* *ZONE*
> 'EST'); -- 11:00 AM EST
All these asterisks make your example unreadable, not to mention
very difficult to copy-and-paste. Please do not do that in future.
Anyway, the problem here is that you are invoking the wrong one of the two
AT TIME ZONE operators. There's one that takes timestamp and returns
timestamptz, which is what you meant to use (or should have, anyway),
but there's also one that takes timestamptz and returns timestamp.
In the absence of any type decoration, the second one will win because
timestamptz is a preferred type. So what you actually got out of that
was
(a) the undecorated literal was presumed to be of time timestamptz,
and since it doesn't mention a UTC offset, was assumed to be in your
timezone, so you got the equivalent of:
regression=# show timezone;
TimeZone
-----------------
America/Chicago
(1 row)
regression=# select '2022-02-07 11:00:00'::timestamptz;
timestamptz
------------------------
2022-02-07 11:00:00-06
(1 row)
(b) AT TIME ZONE rotates that to a timestamp-without-tz,
expressed in the EST zone:
regression=# select '2022-02-07 11:00:00'::timestamptz AT TIME ZONE 'EST';
timezone
---------------------
2022-02-07 12:00:00
(1 row)
Note the lack of any zone indicator in the output --- that's the
easiest way to tell whether a value is timestamp or timestamptz,
though you could also try applying pg_typeof() to the expression.
(c) INSERT casts that back to timestamptz, again assuming your prevailing
zone, so what actually goes into the table is
regression=# select ('2022-02-07 11:00:00'::timestamptz AT TIME ZONE 'EST')::timestamptz;
timezone
------------------------
2022-02-07 12:00:00-06
(1 row)
If you'd cast the unmarked literal to timestamp explicitly, you'd
have got the behavior you're after:
regression=# select ('2022-02-07 11:00:00'::timestamp AT TIME ZONE 'EST')::timestamptz;
timezone
------------------------
2022-02-07 10:00:00-06
(1 row)
> CONFIDENTIALITY NOTICE:
> This message and any attached files from iSeatz,
> Inc. contain information that is confidential and proprietary under
> applicable agreements and/or law.
You really need to point out to your corporate lawyers that this
sort of thing is unenforceable, counterproductive, and silly-looking.
You submitted to a publicly-archived mailing list. If I thought this
notice actually meant anything, I've have been afraid to answer at all.
regards, tom lane
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.