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?


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?

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?


Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld (AT) Askesis (DOT) nl
web: www.askesis.nl

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Joost Kraaijeveld wrote:
Do brackets solve your problem?

WHERE that_time betweeen ...
AND (
rule_name = ...
OR rule_name = ...
)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Joost Kraaijeveld" <J.Kraaijeveld (AT) Askesis (DOT) nl> wrote:
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 = ...)

You have

where rule = ... or (rule = ... and time between ...)

The answer is probably clear by now .

Jim

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Hi Jim and Richard,

Thank for pointing out something that I should have known.

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld (AT) Askesis (DOT) nl
web: www.askesis.nl


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly