Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 18:29, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> Per my previous post a timestamp with timezone is going to be stored as UTC, so there is no ambiguity there. On reflection I realized your concern maybe with determining the original input timezone. That information is not stored by Postgres, so there can be ambiguity as to their value. Doing all timestamps in UTC is one one way to eliminate this. The potential issue I see is that you now push the ambiguity into the app. Namely just from looking at the database values you still do not know what the original timezone the app lives in is.

That’s very true, I hadn’t thought of that.

The use case I have is a schema that unites billing events from disparate telecoms systems from a bunch of different carriers. The source of the non-specific timestamps is party input data files that provide different local timestamps for systems in different systems, but also different apps that submit billing events. So there are many inconsistent feeds into the database.

So when we push the ambiguity into the app, at least what is happening is the the transaction is rejected which means the app breaks. When the app breaks, we can go in and fix the timestamp bug in the particular app. Often this is difficult, and we need to reason about the source data, but the breakage at least tells us that something is wrong. Otherwise we ingress the data, do complex billing queries and then the only time we find out about a bug is when a customer complains about a particular bill. When this happens, it is really difficult to determine whether there is a bug in the query logic or if the input is bogus.

So in this sense, the database is linting the the source data.