pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComparing dates
14+ messages / 12 participants
[nested] [flat]
* Comparing dates
@ 2001-03-06 12:33 Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>
0 siblings, 3 replies; 14+ messages in thread
From: Markus Fischer @ 2001-03-06 12:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing dates
@ 2001-03-06 14:58 dev@archonet.com
parent: patrick.jacquot@anpe.fr
0 siblings, 0 replies; 14+ messages in thread
From: dev@archonet.com @ 2001-03-06 14:58 UTC (permalink / raw)
To: patrick.jacquot@anpe.fr; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing dates
@ 2001-03-06 15:14 Michael Fork <mfork@toledolink.com>
parent: Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>
2 siblings, 0 replies; 14+ messages in thread
From: Michael Fork @ 2001-03-06 15:14 UTC (permalink / raw)
To: Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>; +Cc: 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)
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing dates
@ 2001-03-06 16:38 patrick.jacquot@anpe.fr
parent: Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>
2 siblings, 1 reply; 14+ messages in thread
From: patrick.jacquot@anpe.fr @ 2001-03-06 16:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing dates
@ 2001-03-06 19:28 Jie Liang <jliang@ipinc.com>
parent: Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>
2 siblings, 1 reply; 14+ messages in thread
From: Jie Liang @ 2001-03-06 19:28 UTC (permalink / raw)
To: Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>; +Cc: pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing dates
@ 2001-03-06 22:24 David Lynn <davidl@ayamba.com>
parent: Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 14+ messages in thread
From: David Lynn @ 2001-03-06 22:24 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re:Comparing Dates
@ 2001-03-12 20:03 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2001-03-12 20:03 UTC (permalink / raw)
To: pgsql-sql
David,
Please post your tabledefs and the full query definition. Aside from
the need for an explicit typecast (i.e. '2000-03-02'::date) and the lack
of clarity on month vs. day (March 2 or February 3?), seeing the whole
picture would help.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Comparing Dates
@ 2004-11-18 21:01 Nick Peters <nick.peters@candoltd.com>
0 siblings, 2 replies; 14+ messages in thread
From: Nick Peters @ 2004-11-18 21:01 UTC (permalink / raw)
To: pgsql-sql
Hey,
I am trying to compare dates in a sql statement. this is what i have tried:
SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND
transtype='Sale';
but it returns all rows. When i switch the > with a < it returns
nothing. I have even tried with todays date and have got the same
results. So i guess my question is how do i compare two dates? BTW the
column by the name of shippingdate is a date data type with the same
format as the dates show above.
Thanks in advance.
-Nick
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-18 21:10 Ian Barwick <barwick@gmail.com>
parent: Nick Peters <nick.peters@candoltd.com>
1 sibling, 1 reply; 14+ messages in thread
From: Ian Barwick @ 2004-11-18 21:10 UTC (permalink / raw)
To: Nick Peters <nick.peters@candoltd.com>; +Cc: pgsql-sql
On Thu, 18 Nov 2004 15:01:58 -0600, Nick Peters
<nick.peters@candoltd.com> wrote:
> Hey,
>
> I am trying to compare dates in a sql statement. this is what i have tried:
>
> SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND
> transtype='Sale';
SELECT * FROM transactions WHERE shippingdate> '2004-06-08' AND
transtype='Sale';
Ian Barwick
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-18 21:15 Thomas F.O'Connell <tfo@sitening.com>
parent: Nick Peters <nick.peters@candoltd.com>
1 sibling, 1 reply; 14+ messages in thread
From: Thomas F.O'Connell @ 2004-11-18 21:15 UTC (permalink / raw)
To: Nick Peters <nick.peters@candoltd.com>; +Cc: pgsql-sql
Nick,
You need to quote your date constant value:
'2004-06-08'
select '2004-06-08'::date > 2004-06-08;
?column?
----------
t
(1 row)
select 2004-06-08;
?column?
----------
1990
I'm not exactly sure how the bare string is converted internally, but
it's clearly not a complete date like you're expecting.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 18, 2004, at 3:01 PM, Nick Peters wrote:
> Hey,
>
> I am trying to compare dates in a sql statement. this is what i have
> tried:
>
> SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND
> transtype='Sale';
>
> but it returns all rows. When i switch the > with a < it returns
> nothing. I have even tried with todays date and have got the same
> results. So i guess my question is how do i compare two dates? BTW the
> column by the name of shippingdate is a date data type with the same
> format as the dates show above.
>
> Thanks in advance.
>
> -Nick
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-18 21:34 Nick Peters <nick.peters@candoltd.com>
parent: Ian Barwick <barwick@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Nick Peters @ 2004-11-18 21:34 UTC (permalink / raw)
To: pgsql-sql
Thanks, it turns out that the code that was executing the sql was
flawed. Thanks to all that replied!
-Nick
Ian Barwick wrote:
>On Thu, 18 Nov 2004 15:01:58 -0600, Nick Peters
><nick.peters@candoltd.com> wrote:
>
>
>>Hey,
>>
>>I am trying to compare dates in a sql statement. this is what i have tried:
>>
>>SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND
>>transtype='Sale';
>>
>>
>
>SELECT * FROM transactions WHERE shippingdate> '2004-06-08' AND
>transtype='Sale';
>
>Ian Barwick
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-19 05:50 Greg Stark <gsstark@mit.edu>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 14+ messages in thread
From: Greg Stark @ 2004-11-19 05:50 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: Nick Peters <nick.peters@candoltd.com>; pgsql-sql
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> select 2004-06-08;
> ?column?
> ----------
> 1990
>
> I'm not exactly sure how the bare string is converted internally, but it's
> clearly not a complete date like you're expecting.
What string? That's just integer arithmetic.
--
greg
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-19 06:07 Thomas F.O'Connell <tfo@sitening.com>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Thomas F.O'Connell @ 2004-11-19 06:07 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-sql
Ha. Why so it is. :)
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 18, 2004, at 11:50 PM, Greg Stark wrote:
> "Thomas F.O'Connell" <tfo@sitening.com> writes:
>
>> select 2004-06-08;
>> ?column?
>> ----------
>> 1990
>>
>> I'm not exactly sure how the bare string is converted internally, but
>> it's
>> clearly not a complete date like you're expecting.
>
> What string? That's just integer arithmetic.
>
> --
> greg
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Comparing Dates
@ 2004-11-19 16:05 Guy Fraser <guy@incentre.net>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 14+ messages in thread
From: Guy Fraser @ 2004-11-19 16:05 UTC (permalink / raw)
To: pgsql-sql
You should use single quotes for all literals.
Examples:
select '2004-06-08' ;
?column?
------------
2004-06-08
select 'user' ;
?column?
----------
user
Failing to quote literals will cause unexpected results.
Examples:
select 2004-06-08 ;
?column?
----------
1990
select user ;
current_user
--------------
guy
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> select 2004-06-08;
> ?column?
> ----------
> 1990
>
> I'm not exactly sure how the bare string is converted internally, but
> it's
> clearly not a complete date like you're expecting.
--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2004-11-19 16:05 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-06 12:33 Comparing dates Markus Fischer <mfischer@josefine.ben.tuwien.ac.at>
2001-03-06 15:14 ` Michael Fork <mfork@toledolink.com>
2001-03-06 16:38 ` patrick.jacquot@anpe.fr
2001-03-06 14:58 ` dev@archonet.com
2001-03-06 19:28 ` Jie Liang <jliang@ipinc.com>
2001-03-06 22:24 ` David Lynn <davidl@ayamba.com>
2001-03-12 20:03 Re:Comparing Dates Josh Berkus <josh@agliodbs.com>
2004-11-18 21:01 Comparing Dates Nick Peters <nick.peters@candoltd.com>
2004-11-18 21:10 ` Re: Comparing Dates Ian Barwick <barwick@gmail.com>
2004-11-18 21:34 ` Re: Comparing Dates Nick Peters <nick.peters@candoltd.com>
2004-11-18 21:15 ` Re: Comparing Dates Thomas F.O'Connell <tfo@sitening.com>
2004-11-19 05:50 ` Re: Comparing Dates Greg Stark <gsstark@mit.edu>
2004-11-19 06:07 ` Re: Comparing Dates Thomas F.O'Connell <tfo@sitening.com>
2004-11-19 16:05 ` Re: Comparing Dates Guy Fraser <guy@incentre.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