Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 04:31 AM, Ben Hood wrote:
>
>> On 10 May 2018, at 11:36, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
>>
>> On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote:
>>
>> I dare say it is one of PG's strengths' to be usable as a
>> "linter”.
>
>
> Interesting that you share this view, because after thinking about why I was doing this, using UTC domains is for PG to be 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 ?
>
> OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit timezone qualified timestamps in what language they are written in.

Not really:

https://www.postgresql.org/docs/10/static/datatype-datetime.html

"For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
Time, GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If no
time zone is stated in the input string, then it is assumed to be in the
time zone indicated by the system's TimeZone parameter, and is converted
to UTC using the offset for the timezone zone."

>
>> After all, it is always converted to UTC
>> servside anyway ?
>
> And because of the internal UTC representation, there is no room for ambiguous timezones.

Define ambiguous timezone?

>
>>
>> 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.
>
> So on balance there is no need to use a domain for this?
>
> Or are we saying that domains are one way of achieving the timestamp hygiene, but equally, you can get the same result as described above?
>
> Or is there a specific downside to using a domain for this purpose?
>
> …..maybe I’ve just got a subjective prejudice for using domains to refine and tighten built in data types….
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com