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
