You are correct... My query assumes uniqueness in o.origine and
d.destinazione which is obviously incorrect.
try these two queries, and let me know the results:
INSERT INTO week (address, origbytes, when)
SELECT o.origine, sum(o.bytes), '09/02/1999 00:59:59'::datetime
FROM day o
WHERE o.origine << '194.74.133.0/24' AND
o.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59'
GROUP BY o.origine,3;
UPDATE week
SET destbytes = sum(d.bytes)
FROM day d
WHERE d.destinazione=week.address AND
week.when = '09/02/1999 00:59:59' AND
d.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59'
GROUP BY d.destinazione;
Have you figured out the problem with the problem you were having with
the date around 12:00?
Let me know,
DEJ
> -----Original Message-----
> Thanks again, Jackson, but your query is not right :(
> Here is a sample output:
>
> your query:
>
> SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999
> 00:59:59'::datetime
> FROM day o, day d
> where o.origine=d.destinazione
> and o.origine << '194.74.133.0/24'
> and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
> and d.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
> GROUP BY o.origine,4;
>
> result (only for host 194.74.133.11, query is too long):
>
> 194.74.133.11|18569856|13493249|09/02/1999 00:59:59.00 MET
>
> simple query to check first sum:
>
> SELECT o.origine, sum(o.bytes)
> FROM day o
> where o.origine = '194.74.133.11'
> and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
> GROUP BY o.origine;
>
> 194.74.133.11|250944
>
> Huge difference! Moreover, your query is slow, much much
> slower than two
> separate queries. I would have used it though, if it worked.
> Postgresql
> keeps crashing on my loop. :(
>
> Thanks
>
> L.
>
>