2012
12.31

I have a table like this:

id name starttime endtime
1 Breakfast 01:00:00 11:00:00
2 Lunch 11:00:00 16:00:00
3 Dinner 16:00:00 23:00:00

This query will figure out which time range the current time fits in. In this case, it figures out if we’re in breakfast-zone or lunch-land.

SELECT 
	name,
	IF(CURTIME() BETWEEN starttime AND endtime,1,0) AS active
FROM 
	meal_times

If you’re not used to using BETWEEN, here’s a way using the > and < operators.

SELECT 
	name,
	IF(CURTIME() >= starttime AND CURTIME() <= endtime,1,0) AS active
FROM 
	meal_times

This makes a seperate field called active and sets it to 1 if the meal is within the current time, otherwise it’s 0.

4 comments so far

Add Your Comment
  1. I generally recommend using >= and = and <= if you check the execution path, so it always made sense to me to just write it that way.

    • Great idea, I’ll update the query

  2. For some reason my comment got completely mangled when I submitted it. I suspect they don’t escape the angle brackets properly maybe?

    Anyway, I had written that I use those because it’s more concise to what the actual criteria are, and some people are generally unsure what BETWEEN actually means, whether it’s inclusive or exclusive of the second criteria. So I generally favour the clearer way of writing it.

    • I’ve updated the post that shows both comparison operators :)