Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 22:17, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> I don't understand how this can work. As Francisco demonstrated,
> EXTRACT(TIMEZONE FROM ts) doesn't extract the time zone from the value
> ts, it reports the offset of the client's time zone.


Yes, you and Francisco are right.

If you do:

set time zone 'UTC';
select '2018-05-10 23:17:44-02' :: utc_timestamp;

This will work. But if you change the client zone, it will not:

set time zone 'Asia/Pyongyang’;
select '2018-05-10 23:17:44-02' :: utc_timestamp;

[Code: , SQL State: 23514]  ERROR: value for domain utc_timestamp violates check constraint "utc_timestamp_check"


> What your check probably does is to enforce that the client's time zone
> is set to UTC.

Logically you must be right, given

(a) you cannot extract the zone from the stored value, because the stored value does not contain a zone
(b) the examples that only work when the session zone is set to UTC

Therefore the semantics of EXTRACT(TIMEZONE FROM ts) must rely on the session zone only.

Hence all the check does is to enforce the UTC zone.