Re: Domain based on TIMEZONE WITH TIME ZONE - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Domain based on TIMEZONE WITH TIME ZONE
Date
Msg-id 20180510211741.icfg5xyqjkdvgmgj@hjp.at
Whole thread Raw
In response to Re: Domain based on TIMEZONE WITH TIME ZONE  (Ben Hood <ben@relops.com>)
Responses Re: Domain based on TIMEZONE WITH TIME ZONE  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Domain based on TIMEZONE WITH TIME ZONE  (Ben Hood <ben@relops.com>)
List pgsql-general
On 2018-05-10 21:37:26 +0100, Ben Hood wrote:
>     On 10 May 2018, at 16:33, Francisco Olarte <folarte@peoplecall.com> wrote:
>
>     For what you want to do I think you'll have to parse the text value,
>     maybe by definig a view with a text columns and using some
>     rule/trigger magic for insert / updates.
>
>
> Sorry for being unclear - the solution I have in production appears to work
> with
>
> CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK (EXTRACT(TIMEZONE
> FROM VALUE) = 0);
>
> This raises an exception when an app doesn’t use UTC.

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.

So, if my time zone is set to Europe/Vienna,
extract(timezone from '2018-05-10 23:17:44+00'::timestamptz)
will still return 7200, even though I have explicitely specified a UTC
timestamp.

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

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment

pgsql-general by date:

Previous
From: Steven Lembark
Date:
Subject: Re: Selecting strict, immutable text for a composite type.
Next
From: Tom Lane
Date:
Subject: Re: Selecting strict, immutable text for a composite type.