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@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.

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.klaver@aklaver.com