Skip site navigation (1) Skip section navigation (2)

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$ (view raw, whole thread or download thread mbox)
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

pgsql-novice by date

Next:From: Guido BarosioDate: 2009-01-25 07:05:04
Subject: Re: postgres.exe 100% CPU but no I/O
Previous:From: Jasen BettsDate: 2009-01-25 00:39:09
Subject: Re: removing leading and trailing blanks from every row in a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group