Select hourly MIN and MAX values from mySQL table-CodePudding

I have a table which receives data in every 10 minutes. I want to see MIN and MAX values hourly. I tried this, but this select only daily MIN and MAX values.

SELECT * FROM (SELECT DATE(timestamp) as date, 
           Min(Temp) as MinTemp, 
           Max(Temp) as MaxTemp
           FROM table GROUP BY date DESC LIMIT 100 OFFSET 1) Var1 ORDER BY date ASC

CodePudding user response：

This should get you the grouping you want. You can always add a where clause if you want some subset of rows. It's not clear from your question if you want this for the current day, last 24 hours, or whatever. All those things can be easily accomplished using some variation of NOW or CURDATE and DATE_ADD.

SELECT DATE_FORMAT(timestamp, '%Y-%m-%d-%H') as dateHour, 
Min(Temp) as MinTemp, 
Max(Temp) as MaxTemp
FROM table 
GROUP BY dateHour

CodePudding user response：

You need to use HOUR.

Make sure you still retain the date (otherwise you'll everything for that hour on all dates) and group on the HOUR as well. Example shown below.

SELECT     
       DATE(timestamp) as date, 
       HOUR(timestamp) as hr,
       -- Added in response to comment
       DATE_ADD(
        CAST(timestamp AS DATE), 
        INTERVAL (HOUR(timestamp)) HOUR) as hour_timestamp, 
       Min(Temp) as MinTemp, 
       Max(Temp) as MaxTemp
FROM table
GROUP BY
   DATE(timestamp), 
   HOUR(timestamp)

EDIT. Added extra column in response.