pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat's wrong with my date comparison?
5+ messages / 4 participants
[nested] [flat]
* what's wrong with my date comparison?
@ 2007-10-16 17:57 Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 18:00 ` Re: what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 18:04 ` Re: what's wrong with my date comparison? Andrew Sullivan <ajs@crankycanuck.ca>
2007-10-16 18:09 ` Re: what's wrong with my date comparison? Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 3 replies; 5+ messages in thread
From: Tena Sakai @ 2007-10-16 17:57 UTC (permalink / raw)
To: pgsql-sql
Hi Everybody,
I have a table with a column of timestamp type. It is
known to postgres like this:
name | character varying | not null
value | character varying | not null
datecreated | timestamp without time zone | not null
when I do query
select name, value, datecreated
from mytable
where datecreated > 2007-10-02;
it reports:
name | value | datecreated
----------+----------------------+-------------------------
al_qual | 0 | 2007-08-09 00:06:06.742
srehquan | 3 | 2007-08-09 00:06:06.742
complete | 1 | 2007-08-09 00:06:06.743
al_quan | 0.315924933 | 2007-08-09 00:06:06.742
bsa_qual | 0 | 2007-08-09 00:06:06.743
bsl_qual | 2 | 2007-08-09 00:06:06.743
sh_qual | 0 | 2007-08-09 00:06:06.742
. . . .
. . . .
I don't understand why it thinks August is greater than
October. Can someone please elucidate what is going on?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what's wrong with my date comparison?
2007-10-16 17:57 what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-10-16 18:00 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 20:53 ` Re: what's wrong with my date comparison? Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: Tena Sakai @ 2007-10-16 18:00 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; pgsql-sql
Oooops! I got it.
I was missing quotes.
It must have evaluated 2007-10-02 and used it as a
numerical constant 1995.
Sorry about commotion.
Tena
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Tena Sakai
Sent: Tue 10/16/2007 10:57 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] what's wrong with my date comparison?
Hi Everybody,
I have a table with a column of timestamp type. It is
known to postgres like this:
name | character varying | not null
value | character varying | not null
datecreated | timestamp without time zone | not null
when I do query
select name, value, datecreated
from mytable
where datecreated > 2007-10-02;
it reports:
name | value | datecreated
----------+----------------------+-------------------------
al_qual | 0 | 2007-08-09 00:06:06.742
srehquan | 3 | 2007-08-09 00:06:06.742
complete | 1 | 2007-08-09 00:06:06.743
al_quan | 0.315924933 | 2007-08-09 00:06:06.742
bsa_qual | 0 | 2007-08-09 00:06:06.743
bsl_qual | 2 | 2007-08-09 00:06:06.743
sh_qual | 0 | 2007-08-09 00:06:06.742
. . . .
. . . .
I don't understand why it thinks August is greater than
October. Can someone please elucidate what is going on?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what's wrong with my date comparison?
2007-10-16 17:57 what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 18:00 ` Re: what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-10-16 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-10-16 20:53 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what's wrong with my date comparison?
2007-10-16 17:57 what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-10-16 18:04 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2007-10-16 18:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what's wrong with my date comparison?
2007-10-16 17:57 what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-10-16 18:09 ` Michael Glaesemann <grzm@seespotcode.net>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2007-10-16 18:09 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-16 20:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-16 17:57 what's wrong with my date comparison? Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 18:00 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-10-16 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-16 18:04 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-10-16 18:09 ` Michael Glaesemann <grzm@seespotcode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox