From: | jseymour(at)linxnet(dot)com (Jim Seymour) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Bug in queries ?? |
Date: | 2004-11-23 12:25:18 |
Message-ID: | 20041123122518.38157430E@jimsun.linxnet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl> wrote:
>
> Hi all,
>
>
> I have three questions about 1 table
>
> CREATE TABLE public.logs
> (
> rule_name varchar(32) NOT NULL,
> bytes int8 NOT NULL,
> pkts int8 NOT NULL,
> hostname varchar(100),
> that_time int4 NOT NULL
> ) WITH OIDS;
>
> Question 1.
>
> If I run the following query:
>
> select cast(min(that_time) as abstime), cast(max(that_time) as abstime),
> (sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
> where
> that_time between cast( abstime('2004-10-1 00:00') as int4) and
> cast( abstime('2004-11-1 00:00') as int4)
> and
> rule_name = 'Incoming 83 50 in' or
> rule_name = 'Outgoing 83 50 out'
>
> I expect that the outcome will be between "2004-10-1 00:00" and
> "2004-11-1 00:00" (the month october). However, I get the following result:
>
> min max Totaal in Megabytes
> "2004-09-01 00:00:01+02" "2004-11-23 11:50:01+01"; "82768.623353004456"
>
> The min date is the date of the first entry ever, the max entry the
> last entry ever. Why is this?
Because you're asking "between 1st date and second date and rule_name
equals something," OR rule_name equals something_else.
What you have is equivilent to
where (that between ... and ... and rule = ...) or rule = ...
You want
where time between ... and ... and (rule = ... or rule = ...)
>
>
> Question 2.
>
> If I refrase the above query to:
>
> select cast(min(that_time) as abstime), cast(max(that_time) as abstime),
> (sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
> where
> rule_name = 'Incoming 83 50 in' or
> rule_name = 'Outgoing 83 50 out'
> and
> that_time between cast( abstime('2004-10-1 00:00') as int4) and
> cast( abstime('2004-11-1 00:00') as int4)
>
> I get a diffent answer (see the Totaal in Megabytes):
>
> min max Totaal in Megabytes
> "2004-09-01 00:00:01+02" "2004-11-23 12:00:01+01" "92406.078444480896"
>
> My question why is this?
You have
where rule = ... or (rule = ... and time between ...)
>
> Question 3.
>
> Querying for just "rule_name = 'Incoming 83 50 in'" gives 34990
> Megabytes, just querying for "rule_name = 'Outgoing 83 50 out'" gives
> 5524 Megabytes. How does that compare to the queries above?
The answer is probably clear by now ;).
Jim
From | Date | Subject | |
---|---|---|---|
Next Message | Joost Kraaijeveld | 2004-11-23 12:32:31 | Re: Bug in queries ?? |
Previous Message | Richard Huxton | 2004-11-23 12:07:15 | Re: Bug in queries ?? |