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

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