On 05/10/2018 01:37 PM, Ben Hood wrote:
On 10 May 2018, at 16:33, Francisco Olarte <folarte@xxxxxxxxxxxxxx
<mailto:folarte@xxxxxxxxxxxxxx>> 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@xxxxxxxxxxx
