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