Re: SQL Question: Averages of intervals. - Mailing list pgsql-novice

On 2009-01-23, Daniel Staal <DStaal@usa.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";