Re: Inconsistency of timezones in postgresql

On Wed, Jul 31, 2024 at 6:38 PM Chris BSomething <xpusostomos(at)gmail(dot)com>
wrote:

>
> Riddle me this... I have a field of type timestamp. I do 3 queries on a
> particular record one is AT TIME ZONE 'UTC', ones is AT TIME ZONE
> Australia/Sydney, one is plain, without a clause and I get:
>
> change_time timestamp not null default current_timestamp,
>
> UTC: 2024-01-01 10:42:13
> Australia/Sydney: 2024-01-01 00:42:13:+00
> plain: 2024-01-01 10:42:13
>

Nothing unusual here - though I'd expect the UTC to show +00

You've told the system that your 10am time is actually in Sydney Time Zone
and so it rotates back 10 (11 in my example below) hours to show it to you
in UTC.

> Now I alter table and I alter column change_time type timestamp with
> time zone, so I add the "with time zone clause", now its type is
> "timestamptz", both timestamp and "with time zone" are both stored in UTC
> right? So nothing important should change by altering the column should it?
> But now I get:
>
> UTC: 2024-01-01 02:42:13+00
> Australia/Sydney: 2024-01-01 12:42:13
> plain: 2024-01-01 02:42:13
>
> So all the timestamps are now different. How can that be?
>

Best guess, your table has more than one row and you've chosen a different
row for this example.

Since your example isn't reproducible we'll move onto one that is:

https://dbfiddle.uk/ExHJYT8I

The Sydney time query now rotates forward those same 11 hours since you've
asked the system for what local time it is in Sydney when it is 10am UTC.

Please remember that "at time zone" is a cast, the data type changes when
using it. And for a given time literal the meanings are indeed completely
opposite in effect - it's just the zero and negative zero are the same
value so the difference is not noticed in the UTC test case.

Assuming the time zone for the session is UTC.

And why does the "timestamp" datatype selected "with time zone
> Australia/Sydney" throw a "+00" on the output? I don't understand why that
> scenario in particular gets any +- on the output, but if anything it should
> be "+10" right?
>

As you noted subsequently as being UTC, the offset shown reflects your time
zone setting. A lack of it on the UTC expression is contradictory to my
test.

David J.