Re: Domain based on TIMEZONE WITH TIME ZONE

On 2018-05-10 21:37:26 +0100, Ben Hood wrote:
> On 10 May 2018, at 16:33, Francisco Olarte <folarte(at)peoplecall(dot)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(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>