"k...@rice.edu" <k...@rice.edu> wrote: > On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote:
>> So the standard requires storing of original timezone in the >> data type? I was not aware of that. > > I do not have a copy of the SQL 92 spec, but several references > to the spec mention that it defined the "time zone" as a format > "SHH:MM" where S represents the sign (+ or -), which seems to be > what PostgreSQL uses. I just took a quick look at the spec to refresh my memory, and it seems to require that the WITH TIME ZONE types store UTC (I suppose for fast comparisons), it requires the time zone in the form of a hour:minute offset to be stored with it, so you can determine the local time from which it was derived. I concede that this is not usually useful, and am glad we have a type that behaves as timestamptz does; but occasionally a type that behaves in conformance with the spec would be useful, and it would certainly be less confusing for people who are used to the standard behavior. Basically, both store a moment in time in UTC, and display it with offset in hours and minutes; but the standard says it should show you that moment from the perspective of whoever saved it unless you ask for it in a different time zone, while PostgreSQL always shows it to you from the perspective of your client connection's time zone. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers