to_timestamp() and timestamp without time zone

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.

--
Hernán J. González
http://hjg.com.ar/