Re: to_timestamp() and timestamp without time zone

On 06/23/2011 09:01 AM, hernan gonzalez wrote:
> 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.
>

Rather than being not viable, I'd argue that is is not correct. Rather,
a simple direct cast will suffice:
'2011-12-30 00:30:00'::timestamp without time zone

Every feature and function in PostgreSQL is "potentially dangerous" -
understanding them and using them correctly is the responsibility of the
programmer. Time handling has lots of subtleties that take time to
digest. It appears that you would like a timestamp of 2011-12-30
00:30:00 which you can get. But even so, there are places in the world
where that time exists and other places in the world that it does not.

If you try to force that timestamp into a zone where it doesn't exist,
PostgreSQL makes a reasonable interpretation of the intended point in time.

Cheers,
Steve