Обсуждение: what's wrong with my date comparison?
<p><font size="2">Hi Everybody,<br /><br /> I have a table with a column of timestamp type. It is<br /> known to postgreslike this:<br /><br /> name | character varying | not null<br /> value | charactervarying | not null<br /> datecreated | timestamp without time zone | not null<br /><br /> when Ido query<br /><br /> select name, value, datecreated<br /> from mytable<br /> where datecreated > 2007-10-02;<br/><br /> it reports:<br /><br /> name | value | datecreated <br /> ----------+----------------------+-------------------------<br/> al_qual | 0 | 2007-08-09 00:06:06.742<br/> srehquan | 3 | 2007-08-09 00:06:06.742<br /> complete | 1 | 2007-08-0900:06:06.743<br /> al_quan | 0.315924933 | 2007-08-09 00:06:06.742<br /> bsa_qual | 0 | 2007-08-09 00:06:06.743<br /> bsl_qual | 2 | 2007-08-09 00:06:06.743<br /> sh_qual | 0 | 2007-08-09 00:06:06.742<br /> . . . .<br /> . . . .<br /><br /> I don't understand why it thinks August is greater than<br/> October. Can someone please elucidate what is going on?<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br/><br /></font>
<p><font size="2">Oooops! I got it.<br /> I was missing quotes.<br /> It must have evaluated 2007-10-02 and used it as a<br/> numerical constant 1995.<br /><br /> Sorry about commotion.<br /><br /> Tena<br /><br /><br /> -----Original Message-----<br/> From: pgsql-sql-owner@postgresql.org on behalf of Tena Sakai<br /> Sent: Tue 10/16/2007 10:57 AM<br />To: pgsql-sql@postgresql.org<br /> Subject: [SQL] what's wrong with my date comparison?<br /><br /> Hi Everybody,<br /><br/> I have a table with a column of timestamp type. It is<br /> known to postgres like this:<br /><br /> name | character varying | not null<br /> value | character varying | not null<br/> datecreated | timestamp without time zone | not null<br /><br /> when I do query<br /><br /> select name,value, datecreated<br /> from mytable<br /> where datecreated > 2007-10-02;<br /><br /> it reports:<br /><br/> name | value | datecreated <br /> ----------+----------------------+-------------------------<br/> al_qual | 0 | 2007-08-09 00:06:06.742<br/> srehquan | 3 | 2007-08-09 00:06:06.742<br /> complete | 1 | 2007-08-0900:06:06.743<br /> al_quan | 0.315924933 | 2007-08-09 00:06:06.742<br /> bsa_qual | 0 | 2007-08-09 00:06:06.743<br /> bsl_qual | 2 | 2007-08-09 00:06:06.743<br /> sh_qual | 0 | 2007-08-09 00:06:06.742<br /> . . . .<br /> . . . .<br /><br /> I don't understand why it thinks August is greater than<br/> October. Can someone please elucidate what is going on?<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br/><br /><br /></font>
On Tue, Oct 16, 2007 at 10:57:03AM -0700, Tena Sakai wrote: > select name, value, datecreated > from mytable > where datecreated > 2007-10-02; ^^^^^^^^^^ 2007-10-02 is an arithmetic expression equivalent to 1995. I think what you want is WHERE datecreated > '2007-10-02'; Note the quotes. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland
On Oct 16, 2007, at 12:57 , Tena Sakai wrote: > select name, value, datecreated > from mytable > where datecreated > 2007-10-02; where datecreated > '2007-10-02' 2007-10-02 = 1995. # select current_date < 2007-10-31 as arithmetic_comparison, current_date < '2007-10-31' as date_comparison; arithmetic_comparison | date_comparison -----------------------+----------------- f | t (1 row) Michael Glaesemann grzm seespotcode net
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
> I was missing quotes.
> It must have evaluated 2007-10-02 and used it as a
> numerical constant 1995.
Actually, what you got was a *textual* comparison between '1995' and
the timestamp converted to text, which makes even less sense.
FWIW, as of PG 8.3 you'll get an error:
regression=# select * from timestamp_tbl where d1 > 2007-10-02;
ERROR: operator does not exist: timestamp without time zone > integer
LINE 1: select * from timestamp_tbl where d1 > 2007-10-02; ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
We've seen too many people get burnt by variants of this problem...
regards, tom lane