Re: Simpler question about timestamp

Dennis Gearon <gearond@cvc.net> writes:
> Please correct my limited understanding below:

> timestamp
>     any time zone imput is ignored

Right.  Try it for yourself:

regression=# select '2003-04-04 23:42:57.411432-05'::timestamp;
         timestamp
----------------------------
 2003-04-04 23:42:57.411432
(1 row)

regression=# select '2003-04-04 23:42:57.411432-08'::timestamp;
         timestamp
----------------------------
 2003-04-04 23:42:57.411432
(1 row)

>     time zone demarcation in output is optional

No, time zone just plain isn't there in output.  See above.

>     timestamp value is adjusted to local time zone on output

There is no "adjustment".  The value is what it is, independent of
any time zone.  If you coerce timestamp to timestamptz, then the
value is assumed to be relative to the local time zone; but this
isn't relevant for outputting a timestamp value.

>     CAN BE USED in functional indexes because no timezone change.

This is an oversimplification.  timestamp and timestamptz are equally
indexable.  Transformations of either type to a different form, such
as date, are indexable or not depending on whether the transform has
to assume a particular local timezone.

> timestamptz
>     time zone imput is required

Nope, it ain't.  The default timezone assumption is your local zone as
specified by TimeZone.  For example, where I live:

regression=# select '2003-04-04 23:42:57.411432'::timestamptz;
          timestamptz
-------------------------------
 2003-04-04 23:42:57.411432-05
(1 row)

>     time zone demarcation in output is optional

Nope, it will always appear.

>     timestamp value is adjusted to local time zone on output

Correct.

>     CANNOT BE USED in functional indexes because no timezone change.

See above.

> Why use one or the other?

timestamptz is appropriate for representing actual, specific instants
of time, wherein you can really say that THIS happened THEN; THEN has
a particular representation in each possible timezone.  timestamp has
a much looser meaning, since you can't associate any specific timezone
with it --- it can't be assumed to represent any real-world instant.
"Noon tomorrow" (spoken at an unspecified point on the globe) would be a
concept to equate to timestamp without time zone.  Offhand I think that
airline schedules and such might be the only real-world application
of zoneless times.  Flight 5774 to Buffalo leaves every morning at
8:29, whether it's daylight savings time or not, see?

            regards, tom lane