On 05/12/2018 04:04 AM, Francisco Olarte wrote:
On Thu, May 10, 2018 at 10:37 PM, Ben Hood <ben@xxxxxxxxxx> wrote:
On 10 May 2018, at 16:33, Francisco Olarte <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.
....
This is what I wanted to achieve and the approach so far seems to work.
Can you post an example ( correct, error inputs, preferrably done with psql ) ?
At this point I do not know if it is working or if it seems to working
for you. I see some cases in which it would seem to work, but I would
like to know what you mean by "sending non utc timestamps to the
database".
In the post below Ben explains that he realizes it is not working the
way he thought:
https://www.postgresql.org/message-id/1465CD01-C08A-49BF-BCF3-193E6509A703%40relops.com
From what I can gather it comes down where UTC is being enforced. Ben
was hoping that the domain would force timestamps to be only submitted
with a UTC offset with the idea that this would force storage in the
field as UTC only. The realization is that if you have a field of type
timestamp with time zone the value is going to be stored as UTC
regardless of the offset that is presented(even in cases where there is
no offset presented, when an implicit one is assigned). That means there
really is no need for the domain.
I mean something like the one attached below, which to me seems to
prove it does not work, it just checks that the session timezone is
utc, note the 1st transaction tests an explicit timestamp value,
without conversions, failing in non-utc, the second one checks an
explicit +0000 zone not working in non UTC and the third one checks
anything goes , with or without timestamp, when the time zone is utc.
cdrs=# show timezone;
TimeZone
---------------
Europe/Madrid
(1 row)
cdrs=# begin;
BEGIN
cdrs=# CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK
(EXTRACT(TIMEZONE FROM VALUE) = 0);
CREATE DOMAIN
cdrs=# create table t(ts utc_timestamp);
CREATE TABLE
cdrs=# insert into t values ( current_timestamp );
ERROR: value for domain utc_timestamp violates check constraint
"utc_timestamp_check"
cdrs=# rollback;
ROLLBACK
cdrs=# select current_timestamp;
now
-------------------------------
2018-05-12 12:58:03.616949+02
(1 row)
cdrs=# begin;
BEGIN
cdrs=# CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK
(EXTRACT(TIMEZONE FROM VALUE) = 0);
CREATE DOMAIN
cdrs=# create table t(ts utc_timestamp);
CREATE TABLE
cdrs=# insert into t values ('2015-05-11 10:20:30+0000');
ERROR: value for domain utc_timestamp violates check constraint
"utc_timestamp_check"
cdrs=# rollback;
ROLLBACK
cdrs=# set timezone TO 'UTC';
SET
cdrs=# select current_timestamp;
now
-------------------------------
2018-05-12 10:59:47.946338+00
(1 row)
cdrs=# begin;
BEGIN
cdrs=# CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK
(EXTRACT(TIMEZONE FROM VALUE) = 0);
CREATE DOMAIN
cdrs=# create table t(ts utc_timestamp);
CREATE TABLE
cdrs=# insert into t values ('2015-05-11 10:20:30+0000');
INSERT 0 1
cdrs=# insert into t values ('2015-05-11 10:20:30+0200');
INSERT 0 1
cdrs=# insert into t values ('2015-05-11 10:20:30');
INSERT 0 1
cdrs=# insert into t values (current_timestamp);
INSERT 0 1
cdrs=# select * from t;
ts
-------------------------------
2015-05-11 10:20:30+00
2015-05-11 08:20:30+00
2015-05-11 10:20:30+00
2018-05-12 10:59:54.289827+00
(4 rows)
cdrs=# rollback;
ROLLBACK
Francisco Olarte.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
