MySQL - How to select rows with the min(timestamp) per hour of a given date

Question

I have a table of production readings and need to get a result set containing a row for the min(timestamp) for EACH hour. The column layout is quite simple: ID,TIMESTAMP,SOURCE_ID,SOURCE_VALUE

The data sample would look like:

123,'2013-03-01 06:05:24',PMPROD,12345678.99
124,'2013-03-01 06:15:17',PMPROD,88888888.99
125,'2013-03-01 06:25:24',PMPROD,33333333.33
126,'2013-03-01 06:38:14',PMPROD,44444444.44
127,'2013-03-01 07:12:04',PMPROD,55555555.55
128,'2013-03-01 10:38:14',PMPROD,44444444.44
129,'2013-03-01 10:56:14',PMPROD,22222222.22
130,'2013-03-01 15:28:02',PMPROD,66666666.66

Records are added to this table throughout the day and the source_value is already calculated, so no sum is needed.

I can't figure out how to get a row for the min(timestamp) for each hour of the current_date.

select *
from source_readings
use index(ID_And_Time)
where source_id = 'PMPROD'
and   date(timestamp)=CURRENT_DATE
and   timestamp =
        ( select min(timestamp) 
            from source_readings use index(ID_And_Time)
            where source_id = 'PMPROD'
        )

The above code, of course, gives me one record. I need one record for the min(hour(timestamp)) of the current_date.

My result set should contain the rows for IDs: 123,127,128,130. I've played with it for hours. Who can be my hero? :)

Answer 1

Try below:

SELECT * FROM source_readings 
JOIN 
(
   SELECT ID, DATE_FORMAT(timestamp, '%Y-%m-%d %H') as current_hour,MIN(timestamp) 
   FROM source_readings 
   WHERE source_id = 'PMPROD'
   GROUP BY current_hour
) As reading_min
ON source_readings.ID = reading_min.ID