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(at)peoplecall(dot)com
>> <mailto: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.
>
>>
>> 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(dot)klaver(at)aklaver(dot)com