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

Greg Stark <gsstark(at)mit(dot)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