Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 01:37 PM, Ben Hood wrote:
> 
> 
>> On 10 May 2018, at 16:33, Francisco Olarte <folarte@peoplecall.com 
>> <mailto: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.
> 
>>
>> It seems you want to force your users to use an explcit time zone.
>> This may be better handled above the database.
> 
> This is what I wanted to achieve and the approach so far seems to work.
> 
> It is just that most people think the approach is weird. And this is the 
> reason for the OP - a knowledgable person on a different list saw this 
> approach, thought it was weird and suggested that I should educate 
> myself. Which led me to asking on this list.
> 
> And the consensus appears to be that the approach is weird and that 
> there are other ways to achieve timezone explicitness, one of which is 
> to gain a deep understanding of how Postgres handles and stores timestamps.

Trying to tame time and time zones is maybe quixotic, but not weird.

While I was working on my response to Peter I realized that the below:

CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK 
(EXTRACT(TIMEZONE FROM VALUE) = 0);

might not work the way you expect if you have your server set to 'UTC'.

show timezone;
  TimeZone
----------
  UTC
(1 row)

CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK 
(EXTRACT(TIMEZONE FROM VALUE) = 0);

create table ts_check(id integer, ts_fld utc_timestamp);

insert into ts_check values (1, now());
insert into ts_check values (2, '05/10/18 15:23');
insert into ts_check values (3, '05/10/18 15:23+07');


test=> select * from ts_check ;
  id |            ts_fld
----+-------------------------------
   1 | 2018-05-10 22:37:58.745263+00
   2 | 2018-05-10 15:23:00+00
   3 | 2018-05-10 08:23:00+00

In fact I am not sure how it works:):

set timezone = 'US/Pacific';

test=> insert into ts_check values (5, '05/10/18 15:23');
ERROR:  value for domain utc_timestamp violates check constraint 
"utc_timestamp_check"
test=> insert into ts_check values (6, '05/10/18 15:23+00');
ERROR:  value for domain utc_timestamp violates check constraint 
"utc_timestamp_check"





-- 
Adrian Klaver
adrian.klaver@aklaver.com