Re: Inconsistency of timezones in postgresql

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

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?

And to make matters worse, as I write it is 2024-08-01 01:26 UTC ... or in
other words BOTH scenarios return a date in the future, even though the
field is populated with a default clause of "current_timestamp". select
now() at time zone UTC returns the correct thing.

Is it wrong to assign current_timestamp to a timestamp field because
current_timestamp is "with time zone"? If so, that's amazing since I
thought under the hood its all UTC. I can't see that mentioned in the
documentation that using current_timestamp can be so dangerously wrong when
assigned to a timestamp.

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?

And why did the numbers shift 8 hours later when I changed the data type
of the table? I happen to be 8 hours east of GMT... but I didn't create
this record, if anything it was created by a program and person 10 hours
east. If I do "set time zone 'UTC'" in psql, it changes nothing, so it
doesn't seem to be anything in the psql client that causes the offset.

On Thu, 1 Aug 2024 at 03:03, Chris BSomething <xpusostomos(at)gmail(dot)com> wrote:

>
> Well... I guess then at least we have...
>
> AT TIME ZONE INTERVAL '8 hours'
>
> or indeed...
>
> AT TIME ZONE INTERVAL '+8h'
>
> so at a bare minimum we need documentation that promotes that, and warns
> about UTC+-
>
> I still think Z+- would be a few lines of code that would be a cool fix
> that wouldn't hurt anyone, but anyway.
>
> Chris
>
>
> On Thu, 1 Aug 2024 at 01:42, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Wednesday, July 31, 2024, Chris BSomething <xpusostomos(at)gmail(dot)com>
>> wrote:
>>
>>> Tom Lane said:
>>> "However, notice that the value following TIME ZONE is only allowed to
>>> be an interval by the spec (and this is still true in SQL:2021,
>>> the latest version I have handy). Such an interval is interpreted per
>>> ISO (positive = east of Greenwich)."
>>>
>>> Erm, what do you mean by an interval? If you mean a number, then it’s
>>> broken, because "UTC+10" and "+10" do the same thing. But you seem to be
>>> saying there is indeed some syntax that is interpreted by ISO logic?
>>>
>>
>> There is a named data type called “interval”. He’s referring to that.
>> Neither of those text values is an interval. ‘4 hours 30
>> minutes’::interval is a relevant example.
>>
>> David J.
>>
>>