Unless I'm really misunderstanding, this is just "footgun of timestamp without timezone and implicit coercion"?
The only purpose of the naive timestamp is to defer a necessary step of converting a sort of nominal prototype or template to a real moment on the timeline. Until you pin them down, they don't really represent moments.
It's like having a NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.
IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.
Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.
I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.
Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.
Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.
>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp
surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.
Yes, the SQL syntax is full of misnomers. I agree it should have more hazard tape around it.
The type qualifier "with timezone" doesn't mean it stores a timezone with it! It means the input is interpreted with timezone offset, producing an unambiguous moment on the timeline. You can then compare all such values with a total ordering. But, these stored values do not preserve any offset/locale information. You can't ask "what was the offset of this timestamp when it was input?" That denormalized locale information is stripped when it is interpreted.
The naive type (without timezone) really means that the timezone information is absent and the interpretation is to be deferred. You have to supply timezone information before it can be resolved to the timeline. This is what the implicit coercion is doing in PostgreSQL, mixing in either the session or server timezone offset.
The above is further complicated in that PostgreSQL will supply an implicit (session or server) timezone during interpretation of the input for timestamp with time zone. And conversely, it will ignore timezone or offset even if present in the input for a naive timestamp! I think both of these would be better off handled as type/input errors in a strict mode.
The poorly named "(ts)::timestamptz at time zone 'tz'" construct is the inverse of the input transform that takes a naive timestamp and the given timezone to produce the known moment.
The other sad bit is that all of this is naive about the difference between UTC, TAI, and Unix time standards. There is ambiguity in postulating any future time, since the exact presence of leap seconds is not yet determined.
saltcured · · focus · HN ↗
The only purpose of the naive timestamp is to defer a necessary step of converting a sort of nominal prototype or template to a real moment on the timeline. Until you pin them down, they don't really represent moments.
It's like having a NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.
IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.
Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.
I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.
Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.
Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.
blueplanet200 · · focus · HN ↗
>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp
surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.
saltcured · · focus · HN ↗
The type qualifier "with timezone" doesn't mean it stores a timezone with it! It means the input is interpreted with timezone offset, producing an unambiguous moment on the timeline. You can then compare all such values with a total ordering. But, these stored values do not preserve any offset/locale information. You can't ask "what was the offset of this timestamp when it was input?" That denormalized locale information is stripped when it is interpreted.
The naive type (without timezone) really means that the timezone information is absent and the interpretation is to be deferred. You have to supply timezone information before it can be resolved to the timeline. This is what the implicit coercion is doing in PostgreSQL, mixing in either the session or server timezone offset.
The above is further complicated in that PostgreSQL will supply an implicit (session or server) timezone during interpretation of the input for timestamp with time zone. And conversely, it will ignore timezone or offset even if present in the input for a naive timestamp! I think both of these would be better off handled as type/input errors in a strict mode.
The poorly named "(ts)::timestamptz at time zone 'tz'" construct is the inverse of the input transform that takes a naive timestamp and the given timezone to produce the known moment.
The other sad bit is that all of this is naive about the difference between UTC, TAI, and Unix time standards. There is ambiguity in postulating any future time, since the exact presence of leap seconds is not yet determined.