Re: Comparing dates - Mailing list pgsql-sql

From Michael Fork
Subject Re: Comparing dates
Date
Msg-id Pine.BSI.4.21.0103061007320.17993-100000@glass.toledolink.com
Whole thread Raw
In response to Comparing dates  (Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>)
List pgsql-sql
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)
> 



pgsql-sql by date:

Previous
From: dev@archonet.com
Date:
Subject: Re: Comparing dates
Next
From: Markus Wagner
Date:
Subject: platform independend db access?