CASE inet << inet ... - Mailing list pgsql-hackers
From | The Hermit Hacker |
---|---|
Subject | CASE inet << inet ... |
Date | |
Msg-id | Pine.BSF.4.31.0101161328130.21849-100000@thelab.hub.org Whole thread Raw |
Responses |
Re: CASE inet << inet ...
|
List | pgsql-hackers |
Just trying to summarize some traffic stats, and am either running the query wrong, or you can't do this? The query is: SELECT CASE WHEN to_ip << '216.126.84.0/24' THEN to_ip ELSE from_ip END AS LocalAddr, sum(bytes) as TotalBytes, date_trunc('day',runtime) AS Day FROM stat_log WHERE date_trunc('day', runtime) = '2001-01-02 00:00:00-05' GROUP BY LocalAddr, Day; returns: localaddr | totalbytes | day -----------------+------------+------------------------24.6.125.174 | 13716 | 2001-01-02 00:00:00-0524.43.137.113 | 13140 | 2001-01-02 00:00:00-0524.128.201.128 | 14376 | 2001-01-02 00:00:00-0564.39.38.43 | 14232 | 2001-01-02 00:00:00-05128.11.44.16 | 25050 | 2001-01-02 00:00:00-05130.149.17.13 | 14316 | 2001-01-02 00:00:00-05142.177.197.180 | 179676 | 2001-01-02 00:00:00-05151.164.30.54 | 13260 | 2001-01-02 00:00:00-05166.84.192.39 | 13614 | 2001-01-02 00:00:00-05192.67.198.32 | 13872 | 2001-01-02 00:00:00-05192.245.12.7 | 14676 | 2001-01-02 00:00:00-05193.228.80.12 | 13092 | 2001-01-02 00:00:00-05194.126.24.131 | 21642 | 2001-01-02 00:00:00-05194.209.182.36 | 14448 | 2001-01-02 00:00:00-05195.46.202.129 | 73518 | 2001-01-02 00:00:00-05195.117.86.253 | 13056 | 2001-01-02 00:00:00-05196.38.110.24 | 15012 | 2001-01-02 00:00:00-05202.160.254.40 | 38178 | 2001-01-02 00:00:00-05207.123.82.5 | 15240 | 2001-01-02 00:00:00-05207.136.80.247 | 25290 | 2001-01-02 00:00:00-05208.158.96.110 | 17940 | 2001-01-02 00:00:00-05209.47.145.10 | 2881400 | 2001-01-02 00:00:00-05209.47.148.2 | 3263955 | 2001-01-02 00:00:00-05209.223.182.2 | 222180 | 2001-01-02 00:00:00-05212.43.217.25 | 22974 | 2001-01-02 00:00:00-05216.126.72.6 | 1265472 | 2001-01-02 00:00:00-05216.126.72.30 | 94615 | 2001-01-02 00:00:00-05216.126.84.1 | 201733744 | 2001-01-02 00:00:00-05216.126.84.10 | 151665 | 2001-01-02 00:00:00-05216.126.84.11 | 103630 | 2001-01-02 00:00:00-05216.126.84.14 | 752305 | 2001-01-02 00:00:00-05 Yet: select * from stat_log_holding where from_ip << '216.126.84.0/24'; returns what I'd expect: from_ip | to_ip | port | bytes | runtime ----------------+-----------------+------+----------+------------------------216.126.84.1 | 212.7.160.126 | 873 | 16091760| 2001-01-16 10:53:14-05216.126.84.28 | 195.176.0.212 | 80 | 10247530 | 2001-01-16 10:53:14-05216.126.84.73 | 193.172.127.85 | 80 | 7856477 | 2001-01-16 10:53:14-05216.126.84.73 | 195.149.181.21 | 80 | 6343572 | 2001-01-16 10:53:14-05216.126.84.1 | 216.126.84.253 | 53 | 4401161 | 2001-01-16 10:53:14-05216.126.84.28 | 195.230.44.100 | 80 | 3157811 | 2001-01-16 10:53:14-05216.126.84.95 | 194.206.159.140 | 80 | 3140439 | 2001-01-16 10:53:14-05 So, am I doing something wrong here, as far as that CASE statement is concerned, or is this a bug in v7.0.3 that is fixed in v7.1? Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
pgsql-hackers by date: