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

On Thu, May 10, 2018 at 12:19 PM, Ben Hood <ben@relops.com> wrote:
>> On 10 May 2018, at 09:59, Francisco Olarte <folarte@peoplecall.com> wrote:
....
>> Maybe I'm confussing you more, its not too easy to explain.
>> The point is TIMEZONE is not stored in either of them.
>
> Many thanks for clarification, very much appreciated.
>
> Your point is consistent with the manual: “All timezone-aware dates and times are stored internally in UTC”

Tom lane, which is much more knowledgeable than me, points they are
stored in UTC. Maybe, but, IIRC, they are stored in 8 bytes, I do not
know exactly how, last time I read it that could be either a float8
number or a int8 ( maybe to int4 numbers ). I think they use the same
type of storage as the unix timestamps ( unix timestamp is normally a
signed number of integer seconds from an arbitrary start point,
19700101T000000 UTC, and they designated an instant in time. 7200
designates an instant, I can format it for the reader in many ways,
19700101T020000+0000, "tres de la mañana del primero de enero de mil
novecientos ochenta, hora de Madrid", "1970-01-01 03:00:00 +01" ). But
it is not UTC, it is the designation of an instant in time. Timestamps
do not have time zones. BUT the postgres data types timestamptz is a
way to store a timestamp. So is timestamp. And so is float8. The
difference is how it is converted and interacts with other types.


> Rather it should read “is enforcing the submission of UTC denominated timestamps in the server by using a domain a
sensibleway to enforce a policy that will blow up when apps attempt to use non-UTC timestamps (when they shouldn’t
be)”.
> So the question is not how does the timestamp get stored, rather, is it an anti-pattern to use Postgres as a linter
forapps that forget to use UTC exclusively? 

That poses a problem. You must check the input representation. I
mean..., you were using extract on the value, but this happens ( YMMV
):

cdrs=# show timezone;
   TimeZone
---------------
 Europe/Madrid
(1 row)

cdrs=# select extract(timezone from current_timestamp);
 date_part
-----------
      7200
(1 row)

cdrs=# set timezone TO 'UTC';
SET
cdrs=# select extract(timezone from current_timestamp);
 date_part
-----------
         0
(1 row)

cdrs=# select extract(timezone from '2018-01-01
07:00:00+0100'::timestamptz),  extract(timezone from '2018-01-01
00:00:00-0300'::timestamptz);
 date_part | date_part
-----------+-----------
         0 |         0
(1 row)

cdrs=# set timezone to 'Europe/Madrid';
SET
cdrs=# select extract(timezone from '2018-01-01
07:00:00+0100'::timestamptz),  extract(timezone from '2018-01-01
00:00:00-0300'::timestamptz);
 date_part | date_part
-----------+-----------
      3600 |      3600
(1 row)

cdrs=# select extract(timezone from '2018-01-01
07:00:00+0000'::timestamptz),  extract(timezone from '2018-07-01
00:00:00+0000'::timestamptz);
 date_part | date_part
-----------+-----------
      3600 |      7200
(1 row)

As you can see you are getting the seconds offset for the client, may
be in a domain for the server, timezone at the instant in time
designated by the value. Not what you originally typed to locate the
instant in time.

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.

It seems you want to force your users to use an explcit time zone.
This may be better handled above the database.

Francisco Olarte.