Re: date comparisons

Belinda M. Giardine wrote:
> Thanks that works. But I am trying to understand why the others did not,
> especially my first attempt. Further testing shows that
>
> select id, date_entered from main_table where
> date_entered >= to_timestamp('2006 January', 'YYYY Month');
>
> works, but
>
> select id, date_entered from main_table where
> date_entered >= to_timestamp('January 2006', 'Month YYYY');
>
> does not. The order of the fields in the to_timestamp function changes
> the timestamp produced. Should it be this way?

No. Good testing, you've found a bug. Seems to be a problem with the
"Month" formatting if there's more fields after it.

> hbvar=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> hbvar=# select to_timestamp('2006 January', 'YYYY Month');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)

SELECT to_timestamp('January 22 2006','Month DD YYYY');
to_timestamp
------------------------
0005-06-28 00:00:00+00
(1 row)

SELECT to_timestamp('Jan 22 2006','Mon DD YYYY');
to_timestamp
------------------------
2006-01-22 00:00:00+00
(1 row)

If you report this bug using the form below, I'm sure one of the
developers will have a patch out shortly.
http://www.postgresql.org/support/submitbug

Good catch!
--
Richard Huxton
Archonet Ltd