From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: SQL Question: Averages of intervals. |
Date: | 2009-01-25 01:05:52 |
Message-ID: | glgdtg$7dt$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 2009-01-23, Daniel Staal <DStaal(at)usa(dot)net> wrote:
>
> I have the following table:
>
> LoadTable:
> MachineName - String
> Date - Date
> Time - Time
> IOW - Real
> SYS - Real
> USR - Real
> ... - Real
>
> I am trying to get information out for using in a graph. Unfortunately,
> we are logging several times a minute, so the amounts of data for any
> reasonable timeframe (say, one day) are just too large for any one graph
> to handle well.
>
> So, what I'd like to do is average the IOW (or whichever I'm graphing at
> the moment) over a timeframe. (Where I would probably scale the timeframe
> depending on the total length of time I'm pulling.) So, the result would
> contain one row per interval, with the IOW/whatever being the average
> value over the interval.
>
> Is there any way to do this in straight SQL? (Let's use one day (Date =
> '$date') and 10 minutes as examples: I'll probably be creating an
> interface to generate these ad-hock, over random timeframes.)
10 minutes is 600 seconds
SELECT AVG("IOW"),AVG("SYS"),AVG("USR"),MIN("Time"),"MachineName"
FROM "LoadTable"
WHERE "Date"='today'::date
GROUP BY FLOOR( EXTRACT(EPOCH FROM "Time") / 600 ),"MachineName";
From | Date | Subject | |
---|---|---|---|
Next Message | Guido Barosio | 2009-01-25 07:05:04 | Re: postgres.exe 100% CPU but no I/O |
Previous Message | Jasen Betts | 2009-01-25 00:39:09 | Re: removing leading and trailing blanks from every row in a table |