Re: Domain based on TIMEZONE WITH TIME ZONE

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

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
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
epoch is at 08:00 Beijing time it is equally correct).

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>