From: dev@archonet.com
Date: Tue, 06 Mar 2001 14:58:57 GMT
Message-ID: <20010306.14585700@client.archonet.com>
Subject: Re: Comparing dates
To: <patrick.jacquot@anpe.fr>
Cc: pgsql-sql@postgresql.org
Reply-To: dev@archonet.com
In-Reply-To: <3AA51291.BA386B47@anpe.fr>
References: <20010306133323.A3880@josefine.ben.tuwien.ac.at>
	<3AA51291.BA386B47@anpe.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

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