Thread: Timestamp comparison with string in some special cases

I have just found out that when comparing timestamp with time value
where time value is represented in 24h format + AM/PM sign doesn't work
always.
for instance, the following query works in this format:

select * from table where timestamp_column < '11/19/2007 3:46:09 PM'

and also in this format

select * from table where timestamp_column < '11/19/2007 15:46:09'

BUT: if time is presented in this format, postgres returns an error

ERROR: date/time field value out of range

select * from table where timestamp_column < '11/19/2007 15:46:09 PM'

Now, I know that 24 time format + AM/PM is redundant, but this is how
windows clock format is set up in few of our customers, and this is what
is sent to postgres. We will alter this presentation through our client
program, but is there a way to make postgres accept values like these?
Postgres is version 8.2.3 on fedora 7 (64-bit).

Dragan Matic

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Dragan Matic <mlists@panforma.co.yu> schrieb:

> I have just found out that when comparing timestamp with time value where
> time value is represented in 24h format + AM/PM sign doesn't work always.
> for instance, the following query works in this format:
>
> select * from table where timestamp_column < '11/19/2007 3:46:09 PM'

This isn't a timestamp, this is a simple string. But you can CAST it to
a TIMESTAMP:

test=*# select now() > '11/19/2007 3:46:09 PM'::timestamp;
 ?column?
----------
 t


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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

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?

Dragan Matic



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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

Dragan Matic <mlists@panforma.co.yu> writes:
> 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?

No.  The entire reason for existence of to_timestamp() is to accommodate
translation of formats that are too weird, ambiguous, or inconsistent
to be reasonable for the main timestamp input code to accept.
(Like this one.)

            regards, tom lane