Search Postgresql Archives

Re: Domain based on TIMEZONE WITH TIME ZONE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux