Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 09:59, Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
>
> On Thu, May 10, 2018 at 10:03 AM, Ben Hood <ben(at)relops(dot)com> wrote:
> ...
>> Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE
>> internally?
>
> After reading in the follow up TIMEZONE is a typo for TIMESTAMP, yes, you are.
>
> Aproximately, postgres stores ( virtually ) a point in the time line,
> for both with and without ts types, same format. Something like a real
> number.
>
> The difference is mainly for transforming from/to text ( bear in mind
> when you put a constant in a query you are trasnforming from text ).
> In the with time zone case it formats/expects it as a time string in
> the session configured time zone, in the without case it treats it (
> aproximately ) as if it was in utc ( and then discards the "+00" after
> formating ).
>
> Maybe I'm confussing you more, its not too easy to explain.
>
> The point is TIMEZONE is not stored in either of them.

Many thanks for clarification, very much appreciated.

Your point is consistent with the manual: “All timezone-aware dates and times are stored internally in UTC”

The subtleties of how a TIMESTAMP WITH TIME ZONE is stored versus how it is rendered by a client or used in calculations and queries have been discussed in numerous places.

On reflection, maybe my question was phrased badly.

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?