Hi List,
I need your help.
I'm developing to web page dinamycs using php and postgresql, where in my
database I store information of my stadistics of pings.
The definition of my table is:
CREATE TABLE simon (num serial,ip varchar(16),fecha date,hora time,tras int2,reci int2,lost int2,mini float4,avge
float4,maxifloat4, primary key (num)
);
In my box of linux I run a script with crontab this execute every day 03:30
AM, this script read a mails about stadistics of pings that send another
company and store in my table simon.
I make my reports with php with html so that they are in the Intranet of the
company.
Every minute I recive stadistics of seven sites, for a one day I store 10080
registers and for one site I have 1440 registers.
First Problem
How I can do a back up using pg_dump but store my data only of one month
using a sql example: select * from simon where fecha='2002-05', this form I
can have back up about my information of May, maybe I need reset my serial,
that it begins to store from registry 0.
Second Problem
How I can be created a view or store procedure so that of a one site it can
group every five minutes and have the staditics.
I use in PHP a bucle my time begin 00:00:00 ($h1,$h2)it increases every 5
minutes, my query is select MIN(mini), AVG(avge), MAX(maxi) from simon where
fecha='2002-05-31' and ip=num_ip and hora between $h1 and $h2;
I obtein 208 registers for one day of one site, but this query takes enough
time, I want to optimize my query be fast.
If somebody can help me I will thankfull.
_________________________________________________________________
Descargue GRATUITAMENTE MSN Explorer en
http://explorer.yupimsn.com/intl.asp.