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
