From: dev@archonet.com
To: patrick.jacquot@anpe.fr
Cc: pgsql-sql@postgresql.org
Subject: Re: Comparing dates
Date: Tue, 06 Mar 2001 14:58:57 GMT
Message-ID: <20010306.14585700@client.archonet.com> (raw)
In-Reply-To: <3AA51291.BA386B47@anpe.fr>
References: <20010306133323.A3880@josefine.ben.tuwien.ac.at>
<3AA51291.BA386B47@anpe.fr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, patrick.jacquot@anpe.fr
Subject: Re: Comparing dates
In-Reply-To: <20010306.14585700@client.archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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