Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Ben Hood <ben(at)relops(dot)com> 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 :-)