A bug with the TimeStampTZ data type and the 'AT TIME ZONE' clause

On Friday, February 11, 2022, Troy Frericks <troy.frericks@iseatz.com>
wrote:

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

The time zone you specified is a POSIX one, with the opposite sign
convention than ISO. Use an ISO time zone value.

This is documented.

https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

David J.

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

Thank you!

First, I specified EST & CST which assumed Standard time vs Daylight Saving
time. My bad.

As pointed out in the thread, I needed to change to "American/Chicago" &
"American/New_York".

And a note... When in one timezone, specifying a future point in time in
another timezone, and storing that value in UTC is even more complicated
than first thought. That is, the rules that dictate time zones and that
dictate daylight saving time may change between now and that future point
in time thereby affecting any UTC conversion to a specified time zone. :_(

Following the suggestions in this thread. Here is a working recap...

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

Returns...
|CT |
|-----------------------|
|2022-02-07 10:00:00.000|
|2022-02-07 10:00:00.000|

Troy.
#

On Sat, Feb 12, 2022 at 10:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.