Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/12/2018 04:04 AM, Francisco Olarte wrote:
> On Thu, May 10, 2018 at 10:37 PM, Ben Hood <ben(at)relops(dot)com> wrote:
>> On 10 May 2018, at 16:33, Francisco Olarte <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.
> ....
>> 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(dot)klaver(at)aklaver(dot)com