Re: Timestamp Conversion Woes Redux

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> What differences in _behavior_ are exposed to someone
> _using_ the database, other than ignoring time zone information for the
> "timestamp without time zone" values?

Well, other than the point that changing TimeZone changes the displayed
values of timestamptz but not timestamp, there's the fact that
timestamptz will reject (or change, actually) some values that timestamp
will consider valid. For instance '2005-04-03 02:30' is valid to
timestamp, but in any US timezone it will come out as something
different in timestamptz.

regression=# show timezone;
TimeZone
----------
EST5EDT
(1 row)

regression=# select '2005-04-03 02:30'::timestamp;
timestamp
---------------------
2005-04-03 02:30:00
(1 row)

regression=# select '2005-04-03 02:30'::timestamptz;
timestamptz
------------------------
2005-04-03 03:30:00-04
(1 row)

regards, tom lane