Creating time groups for MySQL time series data

Question

I have a MySQL database with data recorded every 15 minutes. For simplicity, suppose there are 2 fields:

DATETIME Created
Double Value

I would like to draw a chart that is required for each opening hour, min, max and closing values ​​for an hour. To do this, I need to return the results of my MySQL query to my PHP to create JSON. I would like to do this in a MySQL query so that the response is cached.

Here is an example of a problem, given 9 data points trying to get 2-hour groups:

Creation            Value
2014-03-25 12:15:00 413.17011
2014-03-25 12:00:00 414
2014-03-25 11:45:00 415
2014-03-25 11:30:00 415
2014-03-25 11:15:00 415.5
2014-03-25 11:00:00 415.5
2014-03-25 10:45:00 416
2014-03-25 10:30:00 416
2014-03-25 10:15:00 415.99

I will need:

Hour 1 (11:15:00 to 12:15:00)
Open: 415.5
Close: 413.17011
High: 415.5
Low: 413.17011

Hour 2 (10:15:00 to 11:15:00)
Open: 415.99
Close: 415.5
High: 416
Low: 415.5

Of course, this will require repetition within 24 hours, this is just an example. Any help really appreciated!

Here is the current MySQL dump for an example (using MySQL version 2.6.4-pl3):

-- 
-- Table structure for table `exampleTable`
-- 

CREATE TABLE `exampleTable` (
  `created` datetime NOT NULL,
  `value` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `exampleTable`
-- 

INSERT INTO `exampleTable` VALUES ('2014-03-25 12:15:00', 413.17011);
INSERT INTO `exampleTable` VALUES ('2014-03-25 12:00:00', 414);
INSERT INTO `exampleTable` VALUES ('2014-03-25 11:45:00', 415);
INSERT INTO `exampleTable` VALUES ('2014-03-25 11:30:00', 415);
INSERT INTO `exampleTable` VALUES ('2014-03-25 11:15:00', 415.5);
INSERT INTO `exampleTable` VALUES ('2014-03-25 11:00:00', 415.5);
INSERT INTO `exampleTable` VALUES ('2014-03-25 10:45:00', 416);
INSERT INTO `exampleTable` VALUES ('2014-03-25 10:30:00', 416);
INSERT INTO `exampleTable` VALUES ('2014-03-25 10:15:00', 415.99);

Answer 1

SELECT
 DATE(created) AS day,
 HOUR(created) AS hour,
 (
   SELECT Value FROM `table` AS b
   WHERE DATE(a.created) = DATE(b.created)
     AND HOUR(a.created) = HOUR(b.created)
     ORDER BY created ASC LIMIT 1
 ) AS Open,
 (
   SELECT Value FROM `table` AS b
   WHERE DATE(a.created) = DATE(b.created)
     AND HOUR(a.created) = HOUR(b.created)
     ORDER BY created DESC LIMIT 1
 ) AS Close,
 MIN(value) AS Low,
 MAX(value) AS High
FROM `table` AS a
GROUP BY DATE(created), HOUR(created)

DATE + HOUR MIN MAX Low High. Open Close, SQL . , , . .

, , .

Hour 1 (11:15:00 to 12:15:00)
Hour 2 (10:15:00 to 11:15:00)

,

Hour 1 (11:00:00 to 11:59:00)
Hour 2 (10:00:00 to 10:59:00)

15 , (created - INTERVAL 15 MINUTE) created sql .

sqlfiddle .

, : , ( date time). , DATE() created , . , . . sqlfiddle .

Answer 2

,

 FLOOR(( UNIX_TIMESTAMP(myTable.dateCreated) - 900 ) / 3600)

3600 1 , - 900 00:15

MIN() MAX , , min max ( ).

, ( ), , , . , (

SELECT openDate,Open,Close,High,Low 
FROM   (SELECT FLOOR(( UNIX_TIMESTAMP(myTable.dateCreated) - 900 ) / 3600) 
               AS 
                      theHour, 
                      myTable.value AS Open,myTable.dateCreated openDate 
        FROM   myTable 
               JOIN (SELECT value,MIN(dateCreated) AS dateCreated 
                     FROM   myTable 
                     GROUP  BY FLOOR(( UNIX_TIMESTAMP(dateCreated) - 900 ) 
                                     / 3600) 
                    ) AS 
                                    aggTable 
                 ON aggTable.dateCreated = myTable.dateCreated) AS 
       openTable 
       LEFT JOIN (SELECT FLOOR(( UNIX_TIMESTAMP(myTable.dateCreated) - 900 
                               ) / 
                               3600) AS 
       theHour 
       , 
       myTable.value AS Close,myTable.dateCreated closeDate 
       FROM   myTable 
       JOIN (SELECT value,MAX(dateCreated) AS dateCreated 
       FROM   myTable 
       GROUP  BY FLOOR(( UNIX_TIMESTAMP(dateCreated) - 900 ) / 3600) 
       ) AS 
       aggTable 
       ON aggTable.dateCreated = myTable.dateCreated) AS closeTable 
              ON openTable.theHour = closeTable.theHour 
       LEFT JOIN (SELECT 
                                                        FLOOR(( 
                 UNIX_TIMESTAMP(myTable.dateCreated) - 900 ) / 3600) AS 
                   theHour, 
                                                          MAX( 
                                                                  value) 
                 AS High 
                  FROM   myTable 
                  GROUP  BY theHour) AS highTable 
              ON closeTable.theHour = highTable.theHour 
       LEFT JOIN (SELECT 
                                                        FLOOR(( 
                 UNIX_TIMESTAMP(myTable.dateCreated) - 900 ) / 3600) AS 
                   theHour, 
                                                          MIN( 
                                                                  value) 
                 AS Low 
                  FROM   myTable 
                  GROUP  BY theHour) AS lowTable 
              ON highTable.theHour = lowTable.theHour