On 06/23/2011 12:30 PM, hernan gonzalez wrote:
On Thu, Jun 23, 2011 at 4:15 PM, Adrian
Klaver <adrian.klaver@xxxxxxxxx>
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
|
