Re: SQL Question: Averages of intervals.

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";

In response to

Browse pgsql-novice by date

  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