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

On 05/12/2018 10:22 AM, Francisco Olarte wrote:
> Adrian:
> 
> On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>> On 05/12/2018 04:04 AM, Francisco Olarte wrote:
> ...
>>> 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:
> 
> Yep, saw it after posting this.
> 
> 
>>  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 think this problem, and similar ones, come from the description in
> the doc, and in a lot of similar places, as "the timestamp with time
> zone value is stored as utc", or "timestamps with time zones are
> normalized to utc". If you look at the docs you realize they are all
> stored as numbers, and ocupy the same space as timestamp W/O TZ. The
> thing is you do not need UTC for anything to represent timestamps.
> Using the (simpler) unix timestamp as an example. If UTC ( and UAT and
> GMT ) did not exist I could defne it as, say, "number of seconds
> elapsed since eiight in the morning January the Second  in
> Madrid/Spain local clocks plus one hundred and eight thousands", no
> UTC needed at all, they are just numbers, they do not have timezones.
> The only difference is timestamp uses gmtime/timegm for text
> conversion and timestamptz uses localtime/timelocal.

I would agree that timestamp and timestamptz are both stored as numbers.

I would not agree they do not have timezones:

show timezone; 
 

   TimeZone 
 

------------ 
 

  US/Pacific

create table ts_comparison (id integer, ts_tz timestamptz, ts_naive 
timestamp);

insert into ts_comparison values (1, now(), now());
insert into ts_comparison values (1, '05/12/18 11:05', '05/12/18 11:05');
insert into ts_comparison values (1, '05/12/18 11:05-07', '05/12/18 
11:05-07');

select * from ts_comparison ;
  id |             ts_tz             |          ts_naive
----+-------------------------------+----------------------------
   1 | 2018-05-12 11:04:44.161849-07 | 2018-05-12 11:04:44.161849
   1 | 2018-05-12 11:05:00-07        | 2018-05-12 11:05:00
   1 | 2018-05-12 11:05:00-07        | 2018-05-12 11:05:00

A timestamp with time zone anchors the entered value at a point in 
time(UTC timezone) and therefore allows you to recover that point in 
time. From there you can rotate it to whatever timezone you want and 
know that it represents the original point in time. A timestamp(without 
time zone) just records the date and time portions without reference to 
a timezone. This means you have an un-anchored timestamp and a future of 
trying to reconstruct the original point in time. This is a very 
important distinction and the reason why if you care about accurate date 
time's you store as timestamp with time zone. In other words timestamp 
with time zone is more then 'just  a number'.

> 
> In fact I've found the following in the sources:
> 
>    * Timestamps, as well as the h/m/s fields of intervals, are stored as
>    * int64 values with units of microseconds. (Once upon a time they were
>    * double values with units of seconds.)
> 
> And from some reading it seems to be like the unix timestamp, but in
> 64 bits microseconds and referencing extended Julian ( or Gregorian )
> calendar,  not too sure about it. I've read the definition somewhere,
> but thinking of them as "just a number" has avoided me a lot of
> problems.
> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com