Every second I write some data about a current and voltage. I display the data in real time on a chart for a period of 50 seconds, but I'd like to show the data for a longer period of time, for example an hour or two. I'd like to get 50 average points out of this database for the period of an hour. Is it possible to do that in just MySQL, or should I use PHP instead?
This is my SQL structure and some data:
CREATE TABLE IF NOT EXISTS `Danni` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ustroistvo_id` int(11) NOT NULL,
`data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`CurrentA` float NOT NULL,
PRIMARY KEY (`id`),
KEY `ustroistvo_id` (`ustroistvo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1110 ;
INSERT INTO `Danni` (`id`, `ustroistvo_id`, `data`, `CurrentA`) VALUES
(1, 1, '2013-07-02 10:05:35', 58),
(2, 1, '2013-07-02 10:05:37', 57),
(3, 1, '2013-07-02 10:05:38', 46),
(4, 1, '2013-07-02 10:05:39', 54),
(5, 1, '2013-07-02 10:05:40', 58),
(6, 1, '2013-07-02 10:05:41', 56),
(7, 1, '2013-07-02 10:05:42', 40),
(8, 1, '2013-07-02 10:05:44', 53),
(9, 1, '2013-07-02 10:05:45', 59),
(10, 1, '2013-07-02 10:05:46', 51);
Your query can look like this:
SELECT
AVG(Value)
FROM
table
GROUP BY
FLOOR(UNIX_TIMESTAMP(SavedOn) / 72)
SavedOn should be the time you save your Values on.