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

mysql timestamp
sql select most recent record for each id
mysql select latest record for each user
sql select most recent date for each record
how to get latest record in sql by datetime
sql earliest date for each id
postgresql select last record in group
mysql min date

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

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

How to Query Date and Time in MySQL, SELECT now(); -- date and time SELECT curdate(); --date SELECT curtime(); --​time in 24-hour format. To find rows between two dates or timestamps: SELECT This will assume time to be 12am SELECT unix_timestamp('2018-12-09'); -- You can specify an exact timestamp to be converted down to the second SELECT  SELECT now (); -- date and time SELECT curdate (); --date SELECT curtime (); --time in 24-hour format To find rows between two dates or timestamps: SELECT * FROM events where event_date between '2018-01-01' and '2018-01-31' ; -- Can include time by specifying in YYYY-MM-DD hh:mm:ss format: SELECT * FROM events WHERE event_date BETWEEN '2018-01

How to get minimum and maximum for grouped timestamps , I suggest to group by a combination of "hour" and 10-minute interval: SELECT hero , min(timestamp) AS start_time , CASE WHEN count(*) > The CASE expression only adds an end_time if more than one rows You do that by representing timestamps as "number of seconds since a certain date and time". Example : MINUTE() function with current date The following statement will return MINUTEs from the current time which is coming from the CURRENT_TIME(). The first column in the output shows the current time (for reference).

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 HOUR() function, MySQL HOUR() returns the HOUR of a time. HOUR() · LAST_DAY() · LOCALTIME() · LOCALTIMESTAMP() If we get to return the HOUR from the given time 15:13:46, the foreach($dbh->query('SELECT HOUR("15:13:46")') as $row) { echo "<tr>"; Click here to see the MySQL Date and time functions. Just wrap the date() function around your date string which is already in the YYYY-MM-DD format, and also around your timestamp field in the where clause: select * from table where date(`when`) = date('2015-01-05'); From the documentation for date(): Extracts the date part of the date or datetime expression expr.

MySQL MINUTE() function, MySQL MINUTE() returns a MINUTE from a time or datetime value. MySQL MINUTE() function. Last update on February 26 2020 08:08:23 (UTC/GMT +8 hours) mysql> SELECT MINUTE('2009-05-18 10:15:21.000423'); MINUTE("​2009-05-18 10:15:21.000423")') as $row) { echo "<tr>"; echo "<td>" . The most efficient way would be to compare your timestamp (and only timestamp, without using any functions on it) to an expression that can be calculated as constant for every row, this way mysql can use index defined on your timestamp column. SELECT * FROM myTable WHERE last_seen >= NOW() - INTERVAL 10 MINUTE

How do I return a group count for every 15 minutes of the hour on my , Hello all, I have a sql statement that returns a count for every hour on my db, how do I SELECT date_format( timestamp, '%h:00 %p' ) AS HOUR , count(… but the part i really don't understand is… if you create a row every 15 minutes, why do NOT NULL, `timestamp` datetime NOT NULL ) ENGINE=MyISAM DEFAULT​  I want to count the number of times that each time appears within an hour time frame so 5:00 p.m - 5:59:59 pm so 6:00 p.m - 6:59:59 pm so 7:00 p.m - 7:59:59 pm so 8:00 p.m - 8:59:59 pm so in the above result I would get

MySQL NOW() Function, The MySQL NOW() function returns the current date and time in the mysql now minus 1 hour SELECT (NOW() - INTERVAL 1 HOUR) 'NOW - 1 You can use the NOW() function to provide a default value for a DATETIME or TIMESTAMP column. Second, insert a new row into the tmp table without specifying the value for  The advantage I see over using the solution proposed by Justin is that it enables you to select the row with the most recent data per user (or per id, or per whatever) even from subqueries without the need for an intermediate view or table. And in case your running a HANA it is also ~7 times faster :D

Comments
  • Your query is correct. But DATE_FORMAT takes the arguments the other way (mysql doc). Here is the answer in fiddle.
  • 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 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. Got a similar question and refer this answer. Hope it will be useful.