On 06/23/2011 12:30 PM, hernan gonzalez wrote:
On Thu, Jun 23, 2011 at 4:15 PM, Adrian Klaver
<adrian.klaver@gmail.com> wrote:
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.
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
My point is to parse a TIMESTAMP WITHOUT TIME ZONE - and that that should NOT depend on the server/session TIMEZONE.
Try this:
# set TIMEZONE='XXX8';
# select to_timestamp('2007-12-30 00:30:00','YYYY-MM-DD HH24:MI:SS')::timestamp;
2007-12-30 00:30:00
# set TIMEZONE='America/Argentina/Buenos_Aires';
select to_timestamp('2007-12-30 00:30:00','YYYY-MM-DD HH24:MI:SS')::timestamp;
2007-12-30 01:30:00
...snip...
Every example here starts, at its core, with to_timestamp. That function returns a timestamp *with* time zone so of-course the current timezone setting will influence it. Stop using it - it doesn't do what you want.
If you cast directly to a timestamp *without* time zone you can take advantage of the many formats PostgreSQL supports.
See:
http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE for supported formats. Note also that you can use "set datestyle" to match your MDY or DMY date formatting.
If the format you require is so obscure that PostgreSQL can't handle it out-of-the-box (and the one you have presented is completely vanilla), use the many string-handling functions to alter your input as necessary.
Cheers,
Steve