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@xxxxxxxxxxx
