Re: date comparisons

On Tue, 12 Dec 2006, Erik Jones wrote:

> Belinda M. Giardine wrote:
> > This should be simple but I am missing something.  I am trying to extract
> > all records entered after a given date.  The table has a field
> > date_entered which is a timestamp.  In this particular case I am not
> > worried about time.
> >
> > I have tried:
> > select id from main_table where
> > date_entered > to_timestamp('January 2006', 'Month YYYY');
> >
> > select id from main_table where
> > (to_timestamp('January 2006', 'Month YYYY'), now()) overlaps (date_entered, date_entered);
> >
> > Both of these return all the rows in the table.  Half of the rows are
> > dated 2000-06-22 12:00:00.
> >
> > PostgreSQL version 8.1.4
> >
> I think people often make date comparisons too difficult in postgres.
>
> select id
> from main_table
> where date_entered >= '2006-01-01';
>
> There are built in conversions for formatted date strings.
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>

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?

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)

Belinda