Re: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...

Greg Stark <gsstark@mit.edu> writes:
> The documentation I've read makes it sound like these two data types are
> equivalent in every way except for the default timezone assumed interpretation
> when converting to and from textual representations. Is that not true?

I wouldn't think so.  For example, you get dissimilar results near
daylight-savings-time boundaries:

regression=# select '2003-04-06 01:00'::timestamptz + '3 hours'::interval;
        ?column?
------------------------
 2003-04-06 05:00:00-04
(1 row)

regression=# select '2003-04-06 01:00'::timestamp + '3 hours'::interval;
      ?column?
---------------------
 2003-04-06 04:00:00
(1 row)

Now in some sense I suppose you could regard this as strictly a matter
of textual display --- the underlying stored values are indeed three
hours apart in both cases.  But in practice I think it's a lot easier
to regard the datatypes as having distinct semantics.  timestamptz
is "GMT under the hood": it has a definite idea that it is storing an
absolute time with a universal meaning, which it will translate into
the local timezone during I/O.  timestamp just stores the nominal
HH:MM:SS value you give it, with no sense that it knows what time that
really is, and no attempt to correct for different local timezones nor
for daylight-savings changes.  The applications of the two types are
very different.

Because the semantics are in fact different, conversion between
timestamp and timestamptz is not just a binary-equivalent mapping:
there is arithmetic happening in here.  And that's why the previous
suggestion that we could index them interchangeably doesn't fly.

            regards, tom lane