Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 02:17 PM, Peter J. Holzer wrote:
> 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.

It depends on the TimeZone setting in the conf file. So on my machine I
have two instances of Postgres running. One is set to the timezone set
by initdb to 'US/Pacific'. In the other I set TimeZone = 'UTC'. Using
the same client(psql) I get:

'US/Pacific' instance

test=> select now();
now
-------------------------------
2018-05-10 14:47:40.903274-07

test=> select extract(timezone from '05/10/18 14:45+00'::timestamptz);
date_part
-----------
-25200
(1 row)

----------------------------------------

'UTC' instance
postgres=> select now();
now
-------------------------------
2018-05-10 21:47:24.934913+00

postgres=> select extract(timezone from '05/10/18 14:45+00'::timestamptz);
date_part
-----------
0
(1 row)
-
>
> What your check probably does is to enforce that the client's time zone
> is set to UTC.
>
> hp
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com