Re: Domain based on TIMEZONE WITH TIME ZONE

Adrian:

On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)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.

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.