Re: Inconsistency of timezones in postgresql

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

So in what database can you set a timestamp to current_timestamp, then
display it raw, and it's in the future? That's not a bug?

Am I supposed to assign it localtimestamp, because that's not documented
anywhere. And why would it be? Timestamp is an undefined timezone, not a
localtime. At least that wiki article you linked says that, And since we're
told timezones with time zone is UTC internally, why wouldn't assigning it
result in current UTC time? By what logic does it end up in the future? If
there's logic to it, where is that documented? In fact that article says
"people from other databases store UTC times in timestamps", so if
timestamps are there for compatibility with people storing UTC, as it says,
then we should expect timestamps to probably have UTC in them... and when I
use "AT TIME ZONE", it assumes the timestamp is UTC, and makes its
calculations accordingly. So why would I expect localtime to ever raise
its ugly head here?

I do an ALTER COLUMN type timestamptz, and it adds a seemingly random 8
hours to the values... that's not a bug? There is no rhyme or reason for 8
hours to change. Maybe 10 hours, you can make some convoluted argument,
but 8 ? And since "for compatibility" we expect UTC as the most likely
contents of timestamp, why would anything be added to it?

And in any case, the local time of our postgres server is UTC. This record
is created by a trigger in the server. Shouldn't it be UTC in any case?
Even doing "set time zone 'UTC'" in psql doesn't bring more sanity.

And asking a timestamp to display in Australia/Sydney puts +00 on the
output? That's not a bug? What does it mean then? I'm not in
Australia/Sydney, so it can't mean it's +00 from that. The postgres server
is not in that zone, so it can't mean that. The documentation says
timezones are never stored in the data, so it can't mean the data was
created at that zone.

I read that article.... which basically tells us the virtues of
timestamptz, but doesn't say anything about how bad timestamp is other than
the timezone isn't explicit. that's great and all, but if timestamp is as
horribly broken in every respect... even in compatibility with other
databases, just remove the feature. It's unusable. There should be THIS IS
HORRIBLY BROKEN AND SHOULD NEVER BE USED IN ANY SHAPE OR FORM warnings all
over the place in red. And then document all this weird behavior that makes
no sense whatsoever.

On Thu, 1 Aug 2024 at 09:58, 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:
>
>>
>> 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.
>>
>
> Using timestamp without time zone is really the issue here. There is even
> a “don’t do this” entry for it.
>
>
> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times
>
> David J.
>