Thread: Comparing dates
Hello, I've a SELECT statement on many joined Tabled and one of them has a date column called 'date_date'. When I fetch a date e.g. '02-03-2001', I get, say, 60 results back. When I now perform the same query with another date, lets take '03-03-2001', I get back about 70 results. When I now modify my query to get both results in one I write SELECT.... FROM..AND date_date >= '2001-03-02'AND date_date <= '2001-03-03'AND.... I think I should get back the rows for both days, 60 + 70 makes 130 to me. But what I get back is even smaller then 60. I allready tried TO_DATE conversion, an OR construct but always the same result. Is there something special to know when comparing/working with date-datetypes ? kind regards,Markus -- Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ EMail: mfischer@josefine.ben.tuwien.ac.at PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0
Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > .... > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > .... > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: mfischer@josefine.ben.tuwien.ac.at > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ANDing restrictions makes them narrower. If you want to broaden your selection, try ORing the conditions HTH Patrick
On 3/6/01, 4:38:41 PM, <patrick.jacquot@anpe.fr> wrote regarding Re: [SQL] Comparing dates: > Markus Fischer wrote: > > I've a SELECT statement on many joined Tabled and one of them has > > a date column called 'date_date'. When I fetch a date e.g. > > '02-03-2001', I get, say, 60 results back. When I now perform the > > same query with another date, lets take '03-03-2001', I get back > > about 70 results. > > > > When I now modify my query to get both results in one I write > > > > SELECT > > .... > > FROM > > .. > > AND > > date_date >= '2001-03-02' > > AND > > date_date <= '2001-03-03' > > AND > > .... > > > > I think I should get back the rows for both days, 60 + 70 makes > > 130 to me. But what I get back is even smaller then 60. I > > allready tried TO_DATE conversion, an OR construct but always > > the same result. > ANDing restrictions makes them narrower. > If you want to broaden your selection, try ORing the conditions > HTH > Patrick True enough - but in this case he should still get two days' worth. There's something odd here - try AND date_date >= '2001-03-02'::date AND date_date <= '2001-03-03'::date Or even AND (date_date = '2001-03-02'::date OR date_date = '2001-03-03'::date) And see if that helps. The only thing I can think of is that either date_date or the comparisons are being used as a timestamp and there is an invisible time part in there skipping some of the entries. Actually, try: AND date_date >= '2001-03-02' AND date_date < '2001-03-04' (note the < on the second part) If that does it, there are times in there somewhere - Richard Huxton
I am just wildly guessing here, but you initially stated that you queried on '02-03-2001' (Which I read as February 3, 2001 -- and I belive postgres does as well) which returned 60 results, and on '03-03-2001' (March 3, 2001), which returned 70 results. However, that is *not* the query your wrote out, you wrote date_date >= '2001-03-02' (which I would read as March 2, 2001) and date_date <= '2001-03-03' (March 3, 2001) -- which is two entirely different date ranges, and, hence, why you see the seemingly incorrect results. Try this: SELECT count(*) FROM table WHERE date_date = '03-02-2001'::date; SELECT count(*) FROM table WHERE date_date = '03-03-2001'::date; The sum of the above two, should match the count for each of the next two SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date <= '03-03-2001'::date; SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date < '03-04-2001'::date; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > .... > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > .... > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: mfischer@josefine.ben.tuwien.ac.at > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
I think if you cast it then works. e.g. '02-03-2001'::date '02-03-2001'::timestamp Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > .... > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > .... > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: mfischer@josefine.ben.tuwien.ac.at > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Hello - It seems that using BETWEEN would work well, especially for finding dates between two other dates. WHERE date_date BETWEEN '03-02-2001'::date and '03-03-2001'::date --d > On Tue, 6 Mar 2001, Markus Fischer wrote: > > > Hello, > > > > I've a SELECT statement on many joined Tabled and one of them has > > a date column called 'date_date'. When I fetch a date e.g. > > '02-03-2001', I get, say, 60 results back. When I now perform the > > same query with another date, lets take '03-03-2001', I get back > > about 70 results. > > > > When I now modify my query to get both results in one I write > > > > SELECT > > .... > > FROM > > .. > > AND > > date_date >= '2001-03-02' > > AND > > date_date <= '2001-03-03' > > AND > > .... > > > > I think I should get back the rows for both days, 60 + 70 makes > > 130 to me. But what I get back is even smaller then 60. I > > allready tried TO_DATE conversion, an OR construct but always > > the same result. > > > > Is there something special to know when comparing/working with > > date-datetypes ? > > > > > > kind regards, > > Markus > > > > -- > > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > > EMail: mfischer@josefine.ben.tuwien.ac.at > > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)