Re: Domain based on TIMEZONE WITH TIME ZONE - Mailing list pgsql-general

On Thu, May 10, 2018 at 10:37 PM, Ben Hood <ben@relops.com> wrote:
> On 10 May 2018, at 16:33, Francisco Olarte <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.
....
> 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".

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.