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

On 05/13/2018 03:45 PM, Peter J. Holzer wrote:
> On 2018-05-13 12:46:42 -0700, Adrian Klaver wrote:
>> Not trying to trick anyone and no magic. The difference in the represented
>> values between ts_tz and ts_naive is the heart of my argument. Timestamptz
>> values are stored in manner that allows you to have the output with a time
>> zone offset. Timestamps w/notz are not.
> 
> I disagree. The difference isn't in how they are *stored*. We have
> already established that they are stored in the same format.
> 
> The difference is in their *semantics*.

Exactly, timestamptz allows you to retrieve an unambiguous  point in 
time, timestamp does not. We could argue all day about the details, but 
the previous sentence is the important distinction.

> 
> A timestamptz denotes a unique and unambiguous point in time. This point
> in time can be represented in various time zones. So the point in time
> when Apollo 11 launched can be represented as '1969-07-16 09:32:00-04'
> (local time) or '1969-07-16 13:32:00+00' (UTC) or '1969-07-16
> 14:32:00+01' (CET). These are just different ways to denote the same
> point in time - and in fact all three are stored as the same timestamptz
> value (-14552880000000, I think). Only when displaying the value or
> doing certain operations on it is it converted to YMDhmsfz format.
> 
> A timestamp without timezone does NOT denote an unambiguous point in
> time. It is just a compact form of representing a date and time. But
> without any additional context (the location or time zone) this doesn't
> tell you much. '2018-01-01 00:00' in Kiribati was 25 hours before
> '2018-01-01 00:00' in American Samoa.
> 
> 
>>> But timestamps do not have timezone. They are points in the time line.
>>> Points in earth surface have timezones, countries have timezones, but
>>> nor timestamp.
>>
>> I don't know about you but I am living on the earths surface:). That means
>> when I deal with timestamps they are with reference to a location.
> 
> But when you store a timestamp as a timestamptz, you lose that reference
> to a location. All that is left is an abstract reference to a point in
> time. Only when you read that value again (and do certain operations

A point in time anchored to location, the prime meridian. Now I agree 
you lose the original location information, but for many operations that 
is not important as you can reconstitute any location at a later date. 
For those operations where it is important, ideas have been floated on 
this list for dealing with this. I remember a proposal for a composite 
type that included the timestamp and the original timezone. That became 
an actual extension(?) at some point, but I cannot dig it up at the moment.

> with it) is that reference to a location added again - but the current
> location of the reader, not the the original locaton (that is lost
> forever, unless it was stored elsewhere).
> 
> 
>> I will agree that timestamptz is stored as number only. However that number
>> in Postgres has an implied time zone of UTC:
>>
>> https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT
>>
>> "For timestamp with time zone, the internally stored value is always in UTC
>> (Universal Coordinated Time, traditionally known as Greenwich Mean Time,
>> GMT)"
> 
> This is not actually true. There is nothing in the storage format which
> depends on UTC (well, the epoch is at Midnight UTC, at if you say the

Agreed it is a stored number. What makes timestamptz work is that the 
number is relative to Midnight UTC and that the Postgres datetime code 
knows this and uses that knowledge to create unambiguous points in time. 
If you where to throw that number at some other program without any 
context then you would be dealing with just a number. The point is that 
it is being dealt with inside Postgres from a known point of reference 
and so the stored number is more then just a number.

> epoch is at 08:00 Beijing time it is equally correct).
> 
>          hp
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com