Changes to NOW() in 7.2?
Were there any changes to NOW() in 7.2? I see this in HISTORY:
now() returns time with millisecond precision (Thomas)
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 am using JDBC to access the data, if that makes any difference...
Hunter
Hunter Hillegas <lists@lastonepicked.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