First data close to an hour mysql

Question

I have to make a query with the first data close to 8 in the morning per day but sometimes the one at 8 is not there, so I would need the next one.

I use this query

SELECT datos.id_estacion, datos.fecha, hora, datos.valor 
from datos
where id_estacion=1 
and tipo_sensor=3 
and day(fecha)=1 
and hour(hora) in (8,9)
order by fecha, hora
limit 10

And I get this data

    fecha       hors        valor
    2016-01-01  08:45:00    1147,97
    2016-01-01  09:00:00    1147,96
    2016-01-01  09:45:00    1147,96
    2016-02-01  08:00:00    1150,95
    2016-02-01  08:15:00    1150,95
    2016-02-01  08:30:00    1150,95
    2016-02-01  08:45:00    1150,96
    2016-02-01  09:00:00    1150,96
    2016-02-01  09:15:00    1150,96
    2016-02-01  09:30:00    1150,96

But I need the closest to 8

01/01/2016  8:45:00 1147,97
01/02/2016  8:00:00 1150,95

Thanks!

What means "closest" to 8? There is a Range? Between what and what time? Why dont you check minutes as you check hours? or using a between — Leandro Bardelli, Mar 16 '23 at 14:41
Because I need 1 data per day and sometimes the data of 08:00 doesn't exist — CVB, Mar 16 '23 at 14:44
Does this answer your question? [Find closest datetime to specified datetime in mysql query](https://stackoverflow.com/questions/27401399/find-closest-datetime-to-specified-datetime-in-mysql-query) — Leandro Bardelli, Mar 16 '23 at 14:58

FanoFN · Answer 1 · 2023-03-17T01:25:09.100

I suppose you can try using MIN() but you have to make that query as derived table then do a JOIN with datos table to get the accurate valor value. This suppose to work in any MySQL version:

SELECT a.id_estacion, a.fecha, DATE_FORMAT(hora,'%h:%i:%s'), a.valor
  FROM datos a
  JOIN
(SELECT datos.id_estacion, datos.fecha, MIN(datos.hora) MinHora
  FROM datos
WHERE id_estacion=1 
AND tipo_sensor=3 
AND DAY(fecha)=1
  AND HOUR(hora) >= 8
  GROUP BY datos.id_estacion, datos.fecha) b
ON a.id_estacion=b.id_estacion
  AND a.fecha=b.fecha
  AND a.hora=b.MinHora;

Or maybe you can try using ROW_NUMBER().. assuming that you're using MySQL version having that function (v8+):

WITH cte AS(
SELECT datos.id_estacion, datos.fecha, DATE_FORMAT(hora,'%h:%i:%s') AS hora, 
       datos.valor,
       ROW_NUMBER() OVER (PARTITION BY datos.fecha ORDER BY fecha, hora) Rnum
FROM datos
WHERE id_estacion=1 
AND tipo_sensor=3 
AND DAY(fecha)=1
AND HOUR(hora) >= 8)
SELECT id_estacion, fecha, hora, valor
  FROM cte 
  WHERE Rnum=1;

Demo fiddle

First data close to an hour mysql

1 Answers1