SQL: field value based on condition?

Question

I am going to need to return a special value to tell me if the current time is within opening hours:

In table places

ID    OPEN     CLOSE  
1     14:00    16:00  
2     12:00    15:00
3     10:00    14:00

I need to return in the query a special field: PLACE_IS_OPEN - true or false.

so that the returned result when the time is 14:35 is:

ID      PLACE_IS_OPEN
1       1
2       1
3       0

What would be a descent way to go about this?

Answer 1

Test that TIME(NOW()) lies between the open and close times and return its boolean result as a column. This will work as-is if your OPEN,CLOSE are MySQL DATETIME or TIME columns.

SELECT
  ID,
  OPEN,
  CLOSE,
  CASE WHEN TIME(NOW()) BETWEEN `OPEN` AND `CLOSE` THEN 1 ELSE 0 END AS PLACE_IS_OPEN
FROM yourtable

If these are character columns rather than proper TIME types, you will need STR_TO_TIME() to convert them

  CASE WHEN TIME(NOW()) BETWEEN TIME(STR_TO_DATE(`OPEN`, '%H:%i')) AND TIME(STR_TO_DATE(`CLOSE`, '%H:%i')) THEN 1 ELSE 0 END AS PLACE_IS_OPEN

And MySQL should allow you to simplify the CASE statement away, since the BETWEEN will return a 0 or 1 as it is:

SELECT
  ID,
  OPEN,
  CLOSE,
  (TIME(NOW()) BETWEEN TIME(STR_TO_DATE(`OPEN`, '%H:%i')) AND TIME(STR_TO_DATE(`CLOSE`, '%H:%i'))) AS PLACE_IS_OPEN
FROM yourtable

Answer 2

This will return the specified result set:

SELECT t.ID
     , CASE WHEN t.open <= DATE_FORMAT(NOW(),'%H:%i')
             AND t.close > DATE_FORMAT(NOW(),'%H:%i')
       THEN 1 ELSE 0 END AS PLACE_IS_OPEN
  FROM places t
 ORDER BY t.ID

given that the OPEN and CLOSE columns are character datatype, containing strings formatted as 24 hour clock.

(NOTE: using a BETWEEN would give erroneous results on the exact minute of CLOSE. The DATE_FORMAT is going to trim off the seconds portion of the current time, for example, a time '16:00:45', will be formatted as '16:00', which may be equal to the specified CLOSE, but is actually 45 seconds after the actual time specified by CLOSE.)

You also need to ensure that you don't have any rows that "span" over midnight, e.g. open from 10PM to 2AM, where the CLOSE value would otherwise be less than OPEN. (For this query to work, such a period would need to be represented as two separate rows in your table, one row from OPEN to '24:00', and another row from '00:00' to CLOSE.)

Note that a CLOSE time of midnight would need to be represented as '24:00'. (That is, a place open 24 hours could be represented as OPEN='00:00' and CLOSE='24:00'.)

If your data doesn't follow those rules, (for example, midnight of close is represented as 00:00 or CLOSE is less than OPEN, the query would need to be "improved" to handle those conditions.

---

If your OPEN and CLOSE columns are of datatype TIME (rather than character), the query also needs to be modified, using TIME() function in place of the DATE_FORMAT() function. (Also, in this case, you could use a BETWEEN, if it's permissible to return a value of 1 when the current time exactly matches the close time: a current time value of '16:00:00' will equal a CLOSE time of '16:00:00', so it really depends whether you want to return a 1 or a 0 in that case.

Also note that the NOW() function will be evaluated in the current setting of the time_zone variable for your (MySQL) session. (So the OPEN and CLOSE times in that table are effectively being evaluated as if they were specified in the MySQL session timezone.