On 06/23/2011 11:40 AM, hernan gonzalez wrote:
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
That works only for that particular format. The point is that, for
example, if I have some local date time
stored as a string in other format ('30/12/2011 00:30:00') I cannot
reliably parse it as a TIMESTAMP. Which I should.
Works here. I am in US PDT:
select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp with time zone;
to_timestamp
------------------------
2011-12-30 00:30:00-08
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
Thanks for the advice. But it's precisely in the role of a programmer
who has digested a good deal about date-time data and its subtleties,
and who is trying to use in a consistent an robust way date-time data
that I'm asking this question. Or rather, reporting this issue.
. 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.
I strongly disagree. I'm not trying "to force that timestamp into a
zone" at all. I'm just telling postgresl to parse the string '30/12/2011
00:30:00' as a TIMESTAMP (without time zone), that is, to
parse/understand/store it as the abstract/civil (wall calendar+clock)
local datetime "30 dec 2011, 00 30 00 am" with NO association with a
timezone.
Again works here:
test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp without time zone;
to_timestamp
---------------------
2011-12-30 00:30:00
Postgreql does not need to interpret anything here, and indeed
it works pefectly with this datetime if I store it in a TIMESTAMP
WITHOUT TIMEZONE (it stores/manipulates it internally as UTC, but the
programmer doesn't care about it, that is internal).
Actually that is how timestamp with timezone are stored:) If you don't
want to deal with time zones keep tz out of the loop. Store the values
in timestamp without time zone. If you at any point store it in a
timestamp with timezone or cast it to same you will change the value
based on whatever offset is in effect at that time. That is what is
supposed to happen.
IT's only this particular function TO_TIMESTAMP() that have this
problem, because it insists in "interpret" the local date time as a
datetime with timezone (and can't even tell it to use UTC). This is just
wrong.
Yes you can:
test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
') at time zone 'UTC';
timezone
---------------------
2011-12-30 08:30:00
Hernán
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
