I'm yet to find a use case for "WITH TIME ZONE", in all cases it's better to use "WITHOUT TIME ZONE". All it does is displays the date in sql client local timezone, which should never matter for well done service. Would be glad to learn otherwise.
Yes, it is quite confusing and I dread to think how many have got it wrong and store local times like the GP.
But it's also not as simple as "always use WITH TIME ZONE". That also leads to a mistake.
The reason is (just to reiterate what you said) the TIMESTAMP WITH TIME ZONE does not store the time zone! If you ever want to get local time back (e.g. ask a question like "how many users log on before lunch time") then you need to store either local time in a TIMESTAMP WITHOUT TIME ZONE field, or the time zone, and get local time like: SELECT recorded_at AT TIME ZONE time_zone AS local_time ... (I prefer the latter).
Look carefully. The SQL client does not just "display it in local time", it displays it with a UTC offset. You can be sure whenever you see a UTC offset that UTC is fully recoverable. In this way the TIMESTAMP WITH TIME ZONE field is context independent. It's just UTC.
Conversely, if there is no offset, time zone, or something to distinguish it as UTC (like the Z in ISO8601) then you are just storing "local time", that is the time on the clock in someone's kitchen, somewhere. This is the TIMESTAMP WITHOUT TIME ZONE field and is highly context dependent (in particular, what clock was used?)
I'm yet to find a use case for "WITH TIME ZONE", in all cases it's better to use "WITHOUT TIME ZONE". All it does is displays the date in sql client local timezone, which should never matter for well done service. Would be glad to learn otherwise.