[boromisp]

Timestamp with time zone is the type for an "absolute timestamp".

Timestamp without time zone is for local time, and sometimes abused as "time in utc without being explicit about it".

The naming describes the expected input, not what is stored. The time zone name or offset is not stored with timestamptz.

Always use timestamptz, unless you have a specific use case for local time.

[globular-toast]

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