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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Troy Frericks <troy(dot)frericks(at)iseatz(dot)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.