Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote:

> The question should not be “how does Postgres store the timestamp internally”.
>
> Rather it should read “is enforcing the submission of UTC denominated timestamps in the server by using a domain a sensible way to enforce a policy that will blow up when apps attempt to use non-UTC timestamps (when they shouldn’t be)”.
>
> 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?

I dare say it is one of PG's strengths' to be usable as a
"linter".

However, maybe rephrase to:

Is it an anti-pattern to use Postgres as a linter for
apps that forget to use ... timezones *appropriately* ... ?

As long as you can force apps to submit proper timestamp-with-
timezone data is there _really_ a need to care whether apps
do submit in UTC ? After all, it is always converted to UTC
servside anyway ?

In case you want to enforce only ever _handing out_ UTC data
you could wrap the table in a view with forces the output
timezone to UTC and only offers timestamp-withOUT-timezone to
the outside. Then force read access via the view.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346