Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 09:13 AM, Ben Hood wrote:
> 
>> On 10 May 2018, at 15:12, Vick Khera > > wrote:
>>
>> On Thu, May 10, 2018 at 7:31 AM, Ben Hood > > wrote:
>>
>>     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?
>>
>>
>> The *only* way to have timestamp hygiene is to require them to have 
>> time zones at all times, even if that time zone is UTC. Any other 
>> representation of a time is ambiguous without context.
> 
> That makes sense.
> 
> The motivation behind narrowing the built in TIMESTAMP WITH TIME ZONE 
> down to a domain is to ensure the only permissible zone offset is UTC. 
> This would be unambiguous.

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.



-- 
Adrian Klaver
adrian.klaver@aklaver.com