Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 9:13 AM, Ben Hood <ben(at)relops(dot)com> wrote:

> On 10 May 2018, at 15:12, Vick Khera <vivek(at)khera(dot)org> wrote:
>
> On Thu, May 10, 2018 at 7:31 AM, Ben Hood <ben(at)relops(dot)com> 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.
>

​'2018-05-10T15:23:00-07:00​'::timestamptz is unambiguous

Allowing client applications to represent time in the user's timezone is a
feature.

​"""Ben
So to get deterministic timestamps, you could either:

a) make sure the server is always configured to run in UTC;
b) issue SET TIME ZONE ‘UTC’; at the beginning of any application session
"""

No

If I send 4pm ET to the server to be stored in a timestamptz field, and
fail to tell the server that the timezone is ET in the value itself then I
must instead set my session timezone to ET or the server is going to store
the wrong value. There is nothing you can do in an default server to
prevent this. Tom has described how you could possibly make the "fail to
tell the server that the timezone is ET" impossible using a custom type.
This seems to be what you want though I'd question whether it is worth the
cost.

I'm not sure how binary timestamp values being sent to the server in a BIND
command plays into this...

David J.