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:
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.
