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