Re: Timestamp comparison with string in some special cases

On Mon, Nov 19, 2007 at 06:03:36PM +0100, Dragan Matic wrote:
> Sam Mason wrote:
> >On Mon, Nov 19, 2007 at 04:52:10PM +0100, Dragan Matic wrote:
> >
> >>select * from table where timestamp_column < '11/19/2007 15:46:09 PM'
> >>
> >
> >Maybe the to_timestamp() function would help you:
> >
> > SELECT to_timestamp('11/19/2007 15:46:09 PM','MM/DD/YYYY HH24:MI:SS')
> > -> 2007-11-19 15:46:09+00
> >
> >That just ignores the AM/PM flag, which may or may not be what you want
> >to do.
> >
> >
> > Sam
> >
> >
> >
> Tnx, this helped a lot. But not, I am confused with something. I thought
> there supposed to be an implicit conversion from string to timestamp in
> the first case. And isn't this:
>
> SELECT * from table where timestamp_column < to_timestamp('11/19/2007
> 15:46:09 PM','MM/DD/YYYY HH24:MI:SS')
>
> just doing the same thing that implicit string to timestamp conversion
> should have done in the first case?

When you type 'some text' into postgres it's treated as a "literal",
it then uses the type's input function to convert the literal to the
actual internal encoding used by the database. With timestamps, you
have some control over this conversion with timestamps, but not much.
The to_timestamp function treats the literal as text and then parses out
the date with more flexibility.

Does that help?

Sam