to_timestamp() returns a TIMESTAMP WITH TIME ZONE
Perhaps an alternative that returns a TIMESTAMP WITHOUT TIME ZONE (which, BTW, is the default TIMESTAMP)
should be provided. Elsewhere, there is no direct-robust way of parsing a TIMESTAMP WITHOUT TIME ZONE (which
represesents a "local date-time" which behaviour should be totally independent of the timezone set in the server or
session).
Of course, doing a simple cast like this will work ... "almost" always:
db=# select to_timestamp('2011-12-30 00:30:00','YYYY-MM-DD HH24:MI:SS')::timestamp without time zone;
to_timestamp
---------------------
2011-12-30 00:30:00
Here the string is assumed to be the textual representation of a "local date time" (no timezone specified or assumed,
just "the date and the hour that tell the wall calendar and the wall clock"), which is parsed/converted to the proper
type (TIMESTAMP WITHOUT TIME ZONE). But what really happens here is that the string is parsed as a physical
time using an implicit timezone (that of the session), and then, when casted to a plain timezone, the calendar info
is recomputed (with the same TIMEZONE) and then the timezone info discarded. This almost always works as expected,
regardless of the session timezone, because the same timezone is used twice and the dependecy is cancelled...
but not always:
db=# set TIMEZONE='America/Argentina/Buenos_Aires';
db=# select to_timestamp('2007-12-30 00:30:00','YYYY-MM-DD HH24:MI:SS')::timestamp without time zone;
to_timestamp
---------------------
2007-12-30 01:30:00
This is not, then, a fiable way of parsing a TIMESTAMP [WITHOUT TIME ZONE] , and I think it's potentially dangerous.
