How to write query with time between now and midnight of today in MySQL?

Question

I have query like below

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

Is it possible to write query with time between now and midnight of today in MySQL?

Something like below

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

score 18 · Accepted Answer · edited Jan 18 '21 at 12:30

18

I suggest you always use clopen (closed-open) intervals for dates and times comparisons. BETWEEN means closed intervals (from both sides). A very good explanation is in @Aaron Bertrand's blog post: What do BETWEEN and the devil have in common?. Here's a way to write the query:

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

edited Jan 18 '21 at 12:30

Community

1
1

answered Dec 06 '12 at 08:07

ypercubeᵀᴹ

113,259
19
174
235

score 5 · Answer 2 · answered Dec 06 '12 at 07:28

5

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

Current date returns the beginning of the day, then we add 1 day to get to the end of it.

answered Dec 06 '12 at 07:28

Erik Ekman

2,051
12
13

4

This will include `00:00:00` of tomorrow; it's unclear whether that's desirable. – eggyal Dec 06 '12 at 09:08
3

Yes, you probably want to use `where startTime >= now() and startTime < CURRENT_DATE() + INTERVAL 1 DAY` – Erik Ekman Dec 06 '12 at 09:48

score 0 · Answer 3 · answered Dec 06 '12 at 07:30

0

Try this ::

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

answered Dec 06 '12 at 07:30

Sashi Kant

13,277
9
44
71

This is not correct syntax, you need one more argument to `between` – Erik Ekman Dec 06 '12 at 07:33

score 0 · Answer 4 · answered Dec 06 '12 at 08:02

You could try the following: May be it's long though.

Sample Table:

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

score 0 · Answer 5 · answered Dec 06 '12 at 09:09

0

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

answered Dec 06 '12 at 09:09

eggyal

122,705
18
212
237

How to write query with time between now and midnight of today in MySQL?

5 Answers5