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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
