From: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: TWO PROBLEMS
Date: Thu, 06 Jun 2002 17:03:43 +0000
Message-ID: <F2427PEvXbY5koTVK4G00012e43@hotmail.com> (raw)
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,
maxi float4,
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ricardo_jal@hotmail.com
Subject: Re: TWO PROBLEMS
In-Reply-To: <F2427PEvXbY5koTVK4G00012e43@hotmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox