Re: to_timestamp() and timestamp without time zone

On 06/23/2011 12:30 PM, hernan gonzalez wrote:
>
>
> On Thu, Jun 23, 2011 at 4:15 PM, Adrian Klaver
> <adrian(dot)klaver(at)gmail(dot)com <mailto:adrian(dot)klaver(at)gmail(dot)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.
>
>
> 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
>
>
> 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