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@xxxxxx | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment:
signature.asc
Description: PGP signature
