Ben Hood < ben@xxxxxxxxxx> writes: So the question is not how does the timestamp get stored, rather, is it an anti-pattern to use Postgres as a linter for apps that forget to use UTC exclusively?
Well, using a domain to enforce additional constraints on a field's value is certainly not an anti-pattern in itself. But you have to realize that the processing consists of first creating a value of the base type and then applying the constraint expressions of the domain to it. This means you cannot check any details that are lost in the input conversion, because you don't have access to the original input string, only the stored value.
Fair point.
In the case of the domain based on TIMESTAMP WITH TIME ZONE, if you are mandating UTC, what further details could get lost that may have been in the original input string?
The semantics are that inserting into a column of this domain with a non-UTC or absent zone will be rejected and hence is data you don’t want in the database in any case.
As others have explained, Postgres' TIMESTAMP WITH TIME ZONE type doesn't
preserve the input's timezone specification (if any) but forcibly rotates
to UTC and stores just a scalar UTC value. So you can't use a domain to
check anything about whether the input had a timezone field and if so what
it was.
OK, point taken. The intention of the domain is prevent any non-UTC or absent zones, so do you need to check anything after you have inserted it?
(This behavior is nonstandard --- the SQL spec evidently expects the
timezone to be stored explicitly in some fashion --- but I don't foresee
us changing it; we've accumulated too much backwards-compatibility
baggage now.)
If you're sufficiently intent on having checking of that sort, you could
invent your own datatype with your own input function, and then make it
binary-compatible with timestamptz so that you don't need to provide much
else besides the I/O functions. varchar(n) has the same sort of
relationship with text, so there's precedent …
Many thanks for your feedback, much appreciated. What I hear you saying is that is possible and similar things have been done before. But by the same token, just because you can do it, doesn’t necssarily mean it buys you that much.
So if a person of your experience hasn’t come across something like this before, it might mean that on balance it’s not worth the effort and/or potential deviation from standard DB tooling. Be pragmatic.
As an aside, this whole discussion has been super valuable for me to get a better understanding of exactly what is going on. This is precisely the intention of the domain in the first place - to be explicit about timestamps everywhere.
So if I get timestamp UTC explicitness everywhere in my database by actually understanding what is going on as opposed to using a domain, then I have achieved my goal.
I appreciate everybody chiming in on this topic :-)
|
