Re: Domain based on TIMEZONE WITH TIME ZONE

Ben Hood <ben@xxxxxxxxxx> · Thu, 10 May 2018 21:14:00 +0100

On 10 May 2018, at 17:35, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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

That is true.  Mandating UTC is not the only way to eliminate ambiguity. Apologies for appearing to suggest that this is case.

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

Fair point.

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.

The domain approach is what has been implemented and has been running in production for some time without a problem (that we know of).

The cost of this appears to be quite trivial in Postgres - there are 2 lines of DDL in the OP that describe the implementation and it seems to work.

The reason why I started this thread is because somebody on a JDBC related list told me that I was crazy for trying to do this and that I had most likely misunderstood how Postgres works. So respecting their opinion, I decided to ask the Postgres experts.

It is heartening to hear Tom say that the idea is not completely insane, but questions the business value of mandating UTC, given there are other ways to ensure timezone explicitness across the app(s) and the database.

I’m also somewhat wiser about Postgres works now as well.

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

The bit I’m ignorant of is that the domain approach appears to work with the timestampz wire representation. Apps seem to bind using the platform timestampz representation and the server barfs if the app forgets to enforce UTC.