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

On Friday, February 11, 2022, Troy Frericks <troy(dot)frericks(at)iseatz(dot)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.