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? :)

score 3 · Answer 1 · edited Apr 21 '17 at 20:19

3

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

edited Apr 21 '17 at 20:19

scharette

605
1
9
25

answered Mar 01 '13 at 17:44

Minesh

2,284
1
14
22

Your query is correct. But `DATE_FORMAT` takes the arguments the other way ([mysql doc](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format)). [Here](http://sqlfiddle.com/#!2/1ab9e/3) is the answer in fiddle. – chandimak Mar 01 '13 at 18:03
THANKS!!! chandimak, it worked perfecty!!! I'll need to study that doc to figure out why it works. Not sure what re-formting the timestamp with the DATE_FORMAT function is doing, but that seems to be the key to it. – mwm4 Mar 01 '13 at 18:09
@mwm4 I double checked this. This doesn't give the correct answer properly. But, @JW. 's answer returns the correct result. In this answer there is a problem in `group by` values. You can check the error by swapping top 2 timestamp values in the [fiddle](http://sqlfiddle.com/#!2/7dfba/1). Got a similar question and refer [this answer](http://stackoverflow.com/questions/15155178/mysql-get-last-update-values-from-mysql-table/15157018#15157018). Hope it will be useful. – chandimak Mar 01 '13 at 18:37

score 1 · Answer 2 · answered Mar 01 '13 at 18:02

SELECT  a.*
FROM    Table1 a
        INNER JOIN
        (
            SELECT  DATE(TIMESTAMP) date, 
                    HOUR(TIMESTAMP) hour,
                    MIN(TIMESTAMP) min_date
            FROM    Table1
            GROUP   BY DATE(TIMESTAMP), HOUR(TIMESTAMP)
        ) b ON DATE(a.TIMESTAMP) = b.date AND
                HOUR(a.TIMESTAMP) = b.hour AND
                a.timestamp = b.min_date

SQLFiddle Demo

score 0 · Answer 3 · answered Sep 23 '19 at 12:13

With window function:

WITH ranked (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY HOUR(timestamp) ORDER BY timestamp) rn
  FROM source_readings -- original table
  WHERE date(timestamp)=CURRENT_DATE AND source_id = 'PMPROD' -- your custom filter
)
SELECT * -- this will contain `rn` column. you can select only necessary columns
FROM ranked
WHERE rn=1

I haven't tested it, but the basic idea is:

1) ROW_NUMBER() OVER(PARTITION BY HOUR(timestamp) ORDER BY timestamp)

This will give each row a number, starting from 1 for each hour, increasing by timestamp. The result might look like:

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

2) Then on the main query we select only rows with rn=1, in other words, rows that has lowest timestamp in each hourly partition (1st row after sorted by timestamp in each hour).

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

3 Answers3