Average of ten minute period

bokehman · February 1, 2014, 4:31pm

I’ve got a process that updates the table every ten minutes. Now I am trying to graph an average 24 hours over the period of several months. So I need the output to be like this:

average of all entries in each individual column between 00:00-00:10
average of all entries in each individual column between 00:10-00:20
average of all entries in each individual column between 00:02-00:30
average of all entries in each individual column between 00:30-00:40
etc, etc…

How do I do that?

Here’s my table:

CREATE TABLE IF NOT EXISTS snr (
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
10714 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10729 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10744 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10758 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10773 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10788 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10803 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10817 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10832 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10847 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10862 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10876 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10891 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10906 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10921 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10935 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10964 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
10994 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
11023 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
11053 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
11067 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
11097 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’,
11126 float(4,2) UNSIGNED NOT NULL DEFAULT ‘0’
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

r937 · February 1, 2014, 5:13pm

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600) AS `FROM`
     , FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600+600) AS `TO`
     , AVG(`10714`) AS avg_10714
     , AVG(`10729`) AS avg_10729
     , AVG(`10744`) AS avg_10744
     , ...
  FROM snr   
GROUP
    BY FLOOR(UNIX_TIMESTAMP(updated)/600)

bokehman · February 1, 2014, 6:35pm

Thanks!

bokehman · February 1, 2014, 7:08pm

Something wrong there. It only prints out one line. Here’s the output. http://satellites-xml.org/print-signal-level.php

r937 · February 1, 2014, 7:24pm

please confirm that your data actually contains timestamps outside that range
please show your exact query

bokehman · February 1, 2014, 7:35pm

Here’s a dump of the table (select * from table order by updated desc). http://satellites-xml.org/28E-SNR.php
here’s the exact query:

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600) AS FROM
, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600+600) AS TO
, AVG(10714) AS avg_10714
, AVG(10729) AS avg_10729
, AVG(10758) AS avg_10758
, AVG(10773) AS avg_10773
, AVG(10803) AS avg_10803
, AVG(10817) AS avg_10817
, AVG(10832) AS avg_10832
, AVG(10847) AS avg_10847
, AVG(10862) AS avg_10862
, AVG(10876) AS avg_10876
, AVG(10891) AS avg_10891
, AVG(10906) AS avg_10906
, AVG(10921) AS avg_10921
, AVG(10935) AS avg_10935
, AVG(10964) AS avg_10964
, AVG(10994) AS avg_10994
, AVG(11023) AS avg_11023
, AVG(11053) AS avg_11053
, AVG(11067) AS avg_11067
, AVG(11097) AS avg_11097
, AVG(11126) AS avg_11126
FROM snr

r937 · February 1, 2014, 8:02pm

dude, where’s your GROUP BY clause

bokehman · February 1, 2014, 8:27pm

Somehow I left it out, sorry.

Anyway this is not what I wanted.

Imagine I have collected data for a year. I want the average of all the data inserted into 10714 between 00:00 and 00:10. Next line I want the average of all the data inserted into 10714 between 00:10 and 00:20. Etc, etc. And I want this for all columns. So in total it would output 144 lines (6 per hour over 24 hours).

I’ve updated the link above to include the GROUP BY.

r937 · February 1, 2014, 8:34pm

but it’s what you asked for

okay, so you want to disregard date, and focus only on the time, right?

may i ask why you want separate lines for each average? that means instead of a simple query, you are now looking for a humoungous UNION query of 21 different SELECTs

r937 · February 1, 2014, 8:46pm

SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , AVG(`10714`) AS avg_10714
     , AVG(`10729`) AS avg_10729
     , AVG(`10744`) AS avg_10744
     , ...
  FROM snr   
GROUP
    BY FLOOR(TIME_TO_SEC(TIME(updated))/600)

bokehman · February 1, 2014, 9:28pm

Sorry if I wasn’t clear, but it is what I was trying to ask for.

Anyway thanks a lot, it works perfectly.

r937 · February 1, 2014, 9:32pm

you’re welcome

let’s recap what you learned today…

FROM_UNIXTIME()
UNIX_TIMESTAMP()
FLOOR()
TIME()
TIME_TO_SEC()
SEC_TO_TIME()

and
GROUP BY

bokehman · February 10, 2014, 9:05pm

Ok, I’ve got 2 queries.

Query 1 returns my daily trend (since the current records began. Query 2 returns trend over the last 24 hours. Is it possible to combine the two and divergence from the daily trend. For example if daily trend between 00:00 and 00:10 = 13.50, and today between 00:00 and 00:10 = 13.20 the result for that period will be -0.30.

Here are the two queries.

Query 1:
SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , round((AVG(`10773`) + AVG(`10788`) + AVG(`10803`) + AVG(`10817`) + AVG(`10862`) + AVG(`10876`) + AVG(`10921`))/7, 2) AS `2E-QPSK`
  FROM snr
WHERE
    updated &gt; '2014-02-06 06:00:00'
GROUP
    BY FLOOR(TIME_TO_SEC(TIME(updated))/600)


Query 2:
SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , round((`10773` + `10788` + `10803` + `10817` + `10862` + `10876` + `10921`)/7, 2) AS `2E-QPSK`
  FROM snr
WHERE
    updated &gt;= now() - INTERVAL 1 DAY 
ORDER
    BY updated

r937 · February 10, 2014, 10:05pm

query 2 is missing a GROUP BY, so it gives one row per updated value for a 24-hour period… that’s a lot of data

and you want to match each one of those rows with the average for the 10-minute time slot it belongs to?

sounds like a join to me

bokehman · February 10, 2014, 10:28pm

Both queries give 144 rows, one for each ten minute period.

Edit, the above is true, but probably because the new data comes in every 10 minutes. Can a GROUP BY be added if necessary for the query?

bokehman · February 10, 2014, 11:36pm

So if I change query 2 to the following is it possible?

SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
     , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
     , round((`10773` + `10788` + `10803` + `10817` + `10862` + `10876` + `10921`)/7, 2) AS `2E-QPSK`
  FROM snr
WHERE
    updated &gt;= now() - INTERVAL 1 DAY 
GROUP
    BY FLOOR(TIME_TO_SEC(TIME(updated))/600)
ORDER
    BY updated

r937 · February 11, 2014, 10:44am

not really, because you are applying GROUP BY but without using AVG

if the rows are coming in only once every 10 minutes, why do you need to produce averages over 10 minute intervals?

bokehman · February 11, 2014, 5:06pm

I don’t know. I was following your query. There is one data entry per 10 minute interval but it happens every day. So the average was so I could get the average value over several days, and compare it to the result of the last 24 hours.

Anyway thank you very much for your help so far.

r937 · February 11, 2014, 5:27pm

okay, that makes sense

the solution will involve a join

specifically, take your grouped averages query and make it into a subquery, then join it back to the table in the non-grouped query for the last 24 hours

bokehman · February 11, 2014, 8:18pm

I haven’t any idea how to do that. I guess I’ll just have to do 2 queries and sort out the result with PHP.