Re: Inconsistency of timezones in postgresql

>
> The input timestamp is noon. Turn it into a string. Concatenate
> “Australia/Sydney” to it. Cast that to timestamptz. Then rotate that 12pm
> Sydney time to UTC - resulting in 2AM. Print 2am to the screen with a
> “+00” suffix to indicate that what you are seeing is a timestamptz value
> displayed in your UTC specified time zone.
>
> I.e. your just wrote something similar to (in common terms):
>
> Select ‘2012-07-06 12:59:55+10’::timezone at time zone UTC
>
> David J.
>

So what you are saying is that results for timestamp fields will be the
exact opposite of timestamptz fields... i.e. AT TIME ZONE Australia/Sydney
will be earlier than UTC for timestamp vs later than UTC for timestamptz

That seems like a terrible idea....

It also means that it is assuming dates are in the user's time zone.

but... according to the wiki: "Storing UTC values in a timestamp without
time zone column is, unfortunately, a practice commonly inherited from
other databases that lack usable timezone support."

I agree that people using this data type are attempting to store UTC in
there. When should you use timestamp according to the wiki?

"If compatibility with non-timezone-supporting databases trumps all other
considerations."

So if you want to store UTC timestamps for compatibility, use timestamp...
but the database does the opposite, it assumes you're storing localtime,
and then when you go AT TIME ZONE UTC, it goes the wrong direction.

Anyway, isn't the documentation wrong?

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

timestamp without time zone AT TIME ZONE zone → timestamp with time zone
Converts given time stamp without time zone to time stamp with time zone,
assuming the given value is in the named time zone.
timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17
03:38:40+00

Unfortunately the doco doesn't tell us what environmental time zone it is
assuming so the user is not enlightened by the examples.

It says that it assumes that the "value is in the NAMED timezone". What
actually happens is it assumes the value is in your environmental time
zone, and DISPLAYS it in your current zone.

In any case, nobody could read the documentation and not be utterly
confused when they see what postgresql actually does.