How to write a query over time between today and midnight today in MySQL?

Question

I have a request as shown below

SELECT * FROM programs where startTime between now() and now() + INTERVAL 1 DAY; 

Is it possible to write a query over time between today's and midnight versions of MySQL today?

Something like below

 SELECT * FROM programs where startTime between now() and now() + midnight 12; 

Answer 1

I suggest you always use closed (closed) intervals for date and time comparisons. BETWEEN means closed intervals (on both sides). A very good explanation can be found on @Aaron Bertrand's blog : What do BETWEEN and the devil have in common? . Here you can write a request:

 SELECT * FROM programs WHERE startTime >= NOW() AND startTime < CURRENT_DATE() + INTERVAL 1 DAY ; 

Answer 2

 SELECT * FROM programs where startTime between now() and CURRENT_DATE() + INTERVAL 1 DAY; 

The current date returns the beginning of the day, then we add 1 day to get to the end.

Answer 3

Try the following:

 SELECT * FROM programs where startTime between now() and DATE_SUB(CURDATE(), INTERVAL 1 DAY) 

Answer 4

You can try the following: let it be a long time.

Table of examples

 ID STIME ETIME 1 December, 05 2012 05:23:00+0000 December, 05 2012 07:30:00+0000 2 December, 05 2012 10:23:00+0000 December, 05 2012 12:30:00+0000 3 December, 06 2012 22:45:00+0000 December, 07 2012 01:00:00+0000 4 December, 06 2012 22:23:00+0000 December, 06 2012 23:55:00+0000 5 December, 06 2012 20:23:00+0000 December, 06 2012 22:55:00+0000 6 December, 07 2012 10:23:00+0000 December, 07 2012 12:30:00+0000 

Query

 SELECT *,curtime() FROM prog WHERE date(stime) = date(now()) AND date(etime) = date(now()) AND time_to_sec(SUBTIME(etime,'24:00:00')) >= 0 ; 

results

 ID STIME ETIME 4 December, 06 2012 22:23:00+0000 December, 06 2012 23:55:00+0000 5 December, 06 2012 20:23:00+0000 December, 06 2012 22:55:00+0000 

Answer 5

 SELECT * FROM programs WHERE DATE(startTime) = CURRENT_DATE AND startTime > CURRENT_TIMESTAMP