Calculation of average values ​​for date / time sections

Question

Problem:

I have a database with sensor readings with a timestamp for the time the sensor reads. It basically looks like this:

Sensor | Timestamp | Value

Now I want to make a graph from this data, and I want to make several different graphs. Say I want one for the last day, one for the last week and one for the last month. The resolution of each chart will be different, so for the daily chart, the resolution will be 1 minute. For the schedule of the week, it will be one hour, and for the schedule of the month it will be one day, or a quarter of a day.

Therefore, I would like the result to be average for each resolution (for example, Day = Average per minute, Week = Average per hour, etc.)

Example:

Sensor | Start | End | Average

How can I do this quickly and easily in mySQL? I suspect that he creates a temporary table or sorts and combines sensor data with this to get sensor average values? But my knowledge of mySQL is limited at best.

Is there really a smart way to do this?

Answer 1

SELECT  DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp), AVG(value)
FROM    mytable
GROUP BY
        DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp) WITH ROLLUP

WITH ROLLUPhere it produces additional lines with average values ​​for each HOURand DAY, for example:

SELECT  DAY(ts), HOUR(ts), MINUTE(ts), COUNT(*)
FROM    (
        SELECT  CAST('2009-06-02 20:00:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-02 20:30:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-02 21:30:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-03 21:30:00' AS DATETIME) AS ts
        ) q
GROUP BY
        DAY(ts), HOUR(ts), MINUTE(ts) WITH ROLLUP

2, 20, 0, 1
2, 20, 30, 1
2, 20, NULL, 2
2, 21, 30, 1
2, 21, NULL, 1
2, NULL, NULL, 3
3, 21, 30, 1
3, 21, NULL, 1
3, NULL, NULL, 1
NULL, NULL, NULL, 4

2, 20, NULL, 2here means that COUNT(*)is 2for DAY = 2, HOUR = 20and all minutes.

Answer 2

, , 1 :

SELECT sensor,minute(timestamp),avg(value) 
FROM table 
WHERE <time period specifier limits to a single hour>
GROUP BY sensor, minute(timestamp)

Answer 3

, (, )

:

$seconds = 3600;
$start = mktime(...);  // say 2 hrs ago
$end   = .... // 1 hour after $start

SELECT MAX(`when`) AS top_When, MIN(`when`) AS low_When,
   ROUND(AVG(sensor)) AS Avg_S,
   (MAX(`when`) - MIN(`when`)) AS dur, /* the duration in seconds of the actual period */
   ((floor(UNIX_TIMESTAMP(`when`) / $seconds)) * $seconds) as Epoch
   FROM `sensor_stats`
   WHERE `when` >= '$start' AND `when` <= '$end' and duration=30
   GROUP BY Epoch/*((floor(UNIX_TIMESTAMP(`when`) / $seconds)) * $seconds)*/

The advantage of this is that you can have any time periods that you want, and not even require them on “round numbers,” for example, a full hour (even minute, 0-59).