Re: Changes to NOW() in 7.2?

Hunter Hillegas <lists(at)lastonepicked(dot)com> writes:
> I have a query like this:
> SELECT tour_dates.date, WHERE date_of_show >= NOW() ORDER BY date_of_show;
> On 7.1.3, this query would include the current day as well as all dates in
> the future. Since moving to 7.2, it only includes dates in the
> future...

I don't believe it worked like that in 7.1.3 either --- at least not if
date_of_show is of type date. NOW() is generally going to be later than
midnight, which is what a date promotes to when you compare it to a
timestamp.

test71=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

test71=# create table foo (date_of_show date);
CREATE
test71=# insert into foo values ('today');
INSERT 1602743 1
test71=# select * from foo;
date_of_show
--------------
2002-03-04
(1 row)

test71=# select * from foo where date_of_show >= NOW();
date_of_show
--------------
(0 rows)

What you probably want is CURRENT_DATE:

test71=# select * from foo where date_of_show >= current_date;
date_of_show
--------------
2002-03-04
(1 row)

regards, tom lane