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.
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. 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).
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.
Hernán
