mysql select records next to current time

Question

this is a food service system... I have a table named, detalle_regimen_paciente, it stores a diet, with breakfast for monday - sunday (being 1 for monday and 7 for sunday, chilean type) that also have a specific serving time.

+---+---------+-----------+--------------+---+
|id | name    |hora_carga |hora_servicio |day|
+---+---------+-----------+--------------+---+
|1  |breakfast|08:00      |09:00         |1  |
+---+---------+-----------+--------------+---+
|2  |lunch    |10:00      |13:00         |1  |
+---+---------+-----------+--------------+---+
|3  |breakfast|08:00      |09:00         |2  |
+---+---------+-----------+--------------+---+
|2  |lunch    |10:00      |13:00         |2  |
+---+---------+-----------+--------------+---+

so I'm struggling with the query for about two days (theres more in the db than this... some n:m relations being this a consumption table from where I can obtain the total consumption of one employee) and I found that the problem is that I cannot find a way to select just the meal for monday at 08:00 (wich is record id #1) using the current date and current time...

so in a monday at 07:58 it should return just the record with the id 1, cuz it is the next meal to serve, also the next meal to charge for the employee.

+---+---------+-------------+---+
|1  |breakfast|08:00        |1  |
+---+---------+-------------+---+

here is the sql have so far, but it seems to just filters the day...

select detalle_regimen_paciente.hora_carga
from
detalle_regimen_paciente
where
detalle_regimen_paciente.hora_servicio > CURTIME() AND
detalle_regimen_paciente.hora_carga > CURTIME()
AND
detalle_regimen_paciente.dia = (select DAYOFWEEK(CURDATE())-1)

maybe I'm just asking wrong or missusing the time functions.

pls help out here.

thx in advance.

can you define what hora_carga is ? – golja Jul 30 '12 at 22:55 — golja, Jul 30 '12 at 22:55

ypercubeᵀᴹ · Answer 1 · 2012-07-30T23:26:14.130

2

SELECT 
    *                          --- select the columns you need (hora_carga ?)
FROM
  ( SELECT *  
    FROM detalle_regimen_paciente   
    WHERE  hora_servicio > CURTIME()     
         AND
           dia = DAYOFWEEK(CURDATE())-1
       OR 
         dia > DAYOFWEEK(CURDATE())-1
    ORDER BY dia, hora_servicio
      LIMIT 1
  ) AS a
UNION ALL
SELECT *
FROM
  ( SELECT *     
    FROM detalle_regimen_paciente   
    ORDER BY dia, hora_servicio
      LIMIT 1
  ) AS b
ORDER BY dia DESC, hora_servicio DESC
  LIMIT 1
;

edited Jul 30 '12 at 23:26

answered Jul 30 '12 at 23:01

ypercubeᵀᴹ

113,259
19
174
235

Add some kind of modulo 7 comparison for `dia`, and this will be a great answer… – MvG Jul 30 '12 at 23:14
@MvG: yeah, this query will break from Sunday supper up to Sunday midnight. – ypercubeᵀᴹ Jul 30 '12 at 23:15
You might well get rid of one level of nesting, as all those subqueries after the `FROM` parts are unneccessary. – MvG Jul 30 '12 at 23:33
@MvG: I Have the `LIMIT` inside the subqueries so the nesting is necessary. – ypercubeᵀᴹ Jul 30 '12 at 23:36
According to [the docs](http://dev.mysql.com/doc/refman/5.6/en/union.html), parentheses are enough to scope a `LIMIT` to one `SELECT` part of a `UNION`. – MvG Jul 30 '12 at 23:47
@MvG: Oh yes, you are right, this is specific to MySQL syntax. – ypercubeᵀᴹ Jul 30 '12 at 23:49

MvG · Answer 2 · 2012-07-30T23:43:14.147

If your query restricts resultst to both

the current day and
times which still lie in the future

then you can get the nearest of these using

SELECT … FROM … WHERE …
ORDER BY hora_servicio ASC
LIMIT 1

This simply orders the resulting rows by hour and selects the first of them. After the last meal of the day, you'll have to wait till midnight before this query will return a non-empty result. You could probably best fix that using a union:

(SELECT 0 AS tomorrow, … FROM …
 WHERE dia = ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1
   AND hora_servicio > CURTIME()
)
UNION ALL
(SELECT 1 AS tomorrow, … FROM …
 WHERE dia = DAYOFWEEK(CURDATE()) -- this really is the next day
)
ORDER BY tomorrow, hora_servicio
LIMIT 1

You might need this weekday computation for your code as well. Here is how it works:

                                        Su Mo Tu We Th Fr Sa
  DAYOFWEEK(CURDATE())                   1  2  3  4  5  6  7
 (DAYOFWEEK(CURDATE()) + 5)              6  7  8  9 10 11 12
((DAYOFWEEK(CURDATE()) + 5) MOD 7)       6  0  1  2  3  4  5
((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1   7  1  2  3  4  5  6

Your version would compute 0 instead of 7 for sunday. The fact that you can use the MySQL DAYOFWEEK without any magic to refer to the next day might seem a little confusing, so it is certainly worth a comment.

As an alternative to dia = ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1 you might write dia MOD 7 = DAYOFWEEK(CURDATE()) - 1, as this will use zero-based numbers for both sides. However, the result might be less efficient as the database will have to compute the value of the left side, which breaks indexing and similar optimizations. So I'd rather put all the computation into the right hand side.

Thanks to @ypercube for getting me to think about the next day at all.

If you want to consider more than one day into the future (I hope you won't have to do that for too many people), then you could do something like this:

SELECT dia, hora_servicio
FROM detalle_regimen_paciente
WHERE dia <> ((DAYOFWEEK(CURDATE()) + 5) MOD 7) + 1
   OR hora_servicio > CURTIME()
ORDER BY (dia - DAYOFWEEK(CURDATE()) + 1) MOD 7 ASC,
         hora_servicio ASC
LIMIT 1

The idea here is that the first ORDER BY term counts the number of days into the future: this value will be 0 for the current day, 1 for tomorrow, 2 for the day after and so on. For meals from the current day, we only take those after the current time. For all other days, any time will be fine.

mysql select records next to current time

2 Answers2