postgresql - How to calculate bandwidth by SQL query -

i have table this:

-----------+------------+-------  first     | last       | bytes -----------+------------+------- 1441013602 | 1441013602 | 10 -----------+------------+------- 1441013602 | 1441013603 | 20 -----------+------------+------- 1441013603 | 1441013605 | 30 -----------+------------+------- 1441013610 | 1441013612 | 30 

which

• 'first' column switching time of first packet of traffic flow

• 'last' column switching time of last packet of traffic flow

• 'bytes' volume of traffic flow.

how can calculate bandwidth usage each second 1441013602 1441013612 ?

i want this:

1441013602   20 b/s 1441013603   20 b/s 1441013604   10 b/s 1441013605   10 b/s 1441013606    0 b/s 1441013607    0 b/s 1441013608    0 b/s 1441013609    0 b/s 1441013610   10 b/s 1441013611   10 b/s 1441013612   10 b/s 

you can use postgresql's generate_series function this. generate series of rows, 1 each second, since that's want. left join on table of info, 1 row each second each data flow. group by seconds, , sum data flow bytes.

e.g:

select seconds.second, coalesce(sum(t.bytes::float8 / (t.last::float8-t.first::float8+1)),0) generate_series(   (select min(t1.first) table1 t1),   (select max(t1.last) table1 t1) ) seconds(second) left join table1 t   on (seconds.second between t.first , t.last) group seconds.second order seconds.second; 

http://sqlfiddle.com/#!15/b3b07/7

note calculate bytes per second of flow, sum on seconds of flow across flows. gives estimate, since don't know if flow rate steady on flow duration.

for formatting bytes, use format function and/or pg_size_pretty.