[sqlite] 10 minute Avg

[Richard Hipp]

On 11/18/15, Andrew Stewart <AStewart at arguscontrols.com> wrote:
> Hi,
>                 I am trying to generate 10 minute average data for a day
> from a data set.  Table is created by the following:
>
> CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, dateTime
> DATETIME NOT NULL, data INTEGER NOT NULL);
>
> Sample Data
> fwParameterID,dateTime,data
> 1074,2015-11-17 00:00:01,8192
> 1074,2015-11-17 00:33:18,0
>
> Any assistance in generating a query to do 10 minute averages for 'data' on
> November 17 for fwParameterID 1074 would be appreciated.
>

Maybe this:

SELECT substr(dateTime,1,12)||'0:00:00', avg(date)
  FROM dataStreamRecord
 WHERE fwParameterID=1074
      AND dateTime BETWEEN '2015-11-17' AND '2015-11-18'
  GROUP BY substr(dateTime,1,12)
  ORDER BY 1;
-- 
D. Richard Hipp
drh at sqlite.org