Select one piece of data from every day at a specific hour MySQL

Zhiyong's response will work, but wont perform well. You need to figure out a way to get the query to use indexes.

You can add a simple index on timestamp and run the query this way:

SELECT
       d.timestamp, d.*
FROM demo1 d
WHERE 1
 AND d.timestamp > CURDATE() - INTERVAL 30 DAY
 AND hour(d.timestamp) = 16;

In MySQL 5.7 and up, you can created a generated column (also called calculated column) top store the hour of the timestamp in a separate column. You can then index this column, perhaps as a composite index of hour + timestamp, so that the query above will perform really quickly.

ALTER TABLE demo1
ADD COLUMN  hour1 tinyint GENERATED ALWAYS AS (HOUR(timestamp)) STORED,
ADD KEY (hour1, timestamp);

The result query would be:

SELECT
       d.timestamp, d.*
FROM demo1 d
WHERE 1
 AND d.timestamp > CURDATE() - INTERVAL 30 DAY
 AND hour1 = 16;

More info on that here:

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html

Please try the following sql:

select
       d.timestamp, hour(d.timestamp)
from
     demo1 d
where
      DATEDIFF(NOW(), d.timestamp) < 30 and hour(d.timestamp) = 16;

The create sql is as following:

CREATE TABLE `demo1` (
        `id` int(11) not null auto_increment primary key,
        `serverid` int(11) not null,
        `timestamp` datetime not null,
        KEY `idx_timestamp` (`timestamp`)
    ) engine = InnoDB;

insert into `demo1` (serverid, timestamp)
VALUES (1, "2020-07-05 16:20:04"),
       (2, "2020-07-06 17:20:04"),
       (3, "2020-07-07 16:40:04"),
       (4, "2020-07-08 08:20:04"),
       (5, "2020-07-05 15:20:04"),
       (5, "2020-07-05 16:59:04"),
       (5, "2020-06-04 16:59:04");