Re: to_timestamp() and timestamp without time zone

On Thu, 2011-06-23 at 13:26 -0700, Adrian Klaver wrote:

> On 06/23/2011 01:07 PM, Steve Crawford wrote:
> > 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.
>
> Possibly:
> test=> select (to_date('30/12/2007','DD/MM/YYYY') +
> '00:30'::time)::timestamp;
> timestamp
> ---------------------
> 2007-12-30 00:30:00
> (1 row)
>
>
> >
> > Cheers,
> > Steve
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

test=# SET datestyle to DMY;
SET
test=# select '30/12/2011 00:30:00'::timestamp;
timestamp
---------------------
2011-12-30 00:30:00
(1 row)

test=#