Re: Domain based on TIMEZONE WITH TIME ZONE

[Ben Hood <ben@xxxxxxxxxx>]

On 10 May 2018, at 23:43, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

Trying to tame time and time zones is maybe quixotic, but not weird.

Quixotic is a very good description, I’d happily admit that using the UTC domain in this way is not as pragmatic as I thought it would when I introduced it.

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’.

“Might not work the way you expect it to” is the underlying theme and the overall motivation for trying to master time zones from disparate sources.

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:):

As Peter and Francisco has previously demonstrated, I believe that inserting a timestamp into the UTC_TIMESTAMP column:

(1) Checks the client time zone is UTC

(2) Converts the timestamp to UTC, whether it was presented as UTC or not.

Going to back to the original intention of enforcing UTC, the end result is correct, but for the wrong reason.

In this example, row 3 contains the UTC value of  '05/10/18 15:23+07’, which is the end result I wanted (pure UTC everywhere).

But, the way that it is actually happens under the covers is masked by the use of the UTC_TIMESTAMP domain.

In the scenario, there is a belief that only UTC timestamps are being inserted, which is not true.

The way that Postgres stores any timestamp leads to the desired result, fooling me into believing that the UTC_TIMESTAMP domain is casual for the correct result.

I would have need check whether the behavior is the same using a network client driver (maybe there is some implicit coercion going on when psql parses the statement).

But I think the point still stands that Postgres timestamp handling is doing the real work and is casual for the desired outcome, not the domain.