CODEWITHSUNDEEP
mysql
Ahmed Memon
Ahmed Memon

Reputation: 319

mySQL Off business hours records

My system needs to respond to customers, based upon on or off business hours;

I created the following table;

CREATE TABLE responses (
  response_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  start_at time DEFAULT NULL,
  end_at time DEFAULT NULL,
  PRIMARY KEY (response_id)
);

The business hour query work fine;

SELECT * FROM responses WHERE CURTIME() BETWEEN start_at AND end_at;

Off Business Hours: Notice start time is 8:01 pm while 8:00 am is end time. In this case, start time is smaller than end time, but hand in hand, it changes the day as well.

In this scenario, what is the best way to write the mySQL SELECT Query?

Upvotes: 0

Views: 42

Answers (1)

Nosajimiki
Nosajimiki

Reputation: 1103

Use NOW() and dateTime fields instead of CURTIME() and Time fields.

Right now you are comparing something like this:

WHERE '22:00:00' BETWEEN '20:00:01' AND '8:00:01'; // returns FALSE

But when you use dateTime across days it does something like this:

WHERE '2018-10-23 22:00:00'  BETWEEN '2018-10-23 20:00:01' AND '2018-10-24 8:00:01' // returns TRUE

Upvotes: 1

Related Questions