Re: Inconsistency of timezones in postgresql

On Wednesday, July 31, 2024, Chris BSomething <xpusostomos(at)gmail(dot)com> wrote:

>
> So... the wiki (not the documentation mind you) says "don't use timestamp"
> because it’s there for compatibility with other databases?
>

The docs focus on what is and try to generally withhold judgement. This
particular area is also quite challenging to address and the motivation to
want to make the effort drops significantly when the tone and quality of
the instigating bug report is so unfavorable.

> and when I use "AT TIME ZONE", it assumes the timestamp is UTC, and makes
> its calculations accordingly.
>

The system rarely assumes things about user data. In this case the session
time zone setting and the various ways to specify time zones exist because
we choose not to assume. The application may store whatever time literal
it wants in a timestamp field and is promised to get that exact same value
out upon retrieval no matter their time zone setting. That’s all it is
documented to do as a data type. Everything else is functions.

Timestamptz is documented to perform rotations according to the time zone
setting upon producing text output. And it tells you what zone it ended up
in.

I do an ALTER COLUMN type timestamptz, and it adds a seemingly random 8
> hours to the values... that's not a bug?
>

As noted on the other reply the bug seems to be in your test setup, not the
system.

David J.