Datetime equal or greater than today in MySQL

Question

What's the best way to do following:

SELECT * FROM users WHERE created >= today;

Note: created is a datetime field.

score 400 · Accepted Answer · edited Feb 28 '22 at 20:17

400

SELECT * FROM users WHERE created >= CURDATE();

But I think you mean created < today

You can compare datetime with date, for example: SELECT NOW() < CURDATE() gives 0, SELECT NOW() = CURDATE() gives 1.

edited Feb 28 '22 at 20:17

questionto42

7,175
4
57
90

answered Mar 03 '11 at 14:52

Clodoaldo Neto

118,695
26
233
260

16

`CURDATE()` return only date not time – Shakti Singh Mar 03 '11 at 14:54
23

@n00b how do you have users who are created in the future..? interesting =) I was using this to see if a post had expired yet. – That Realty Programmer Guy Mar 18 '14 at 03:52

score 94 · Answer 2 · edited Jun 21 '23 at 12:35

94

SELECT * FROM myTable WHERE  DATE(myDate) = DATE(NOW())

edited Jun 21 '23 at 12:35

Rich Steinmetz

1,020
13
28

answered Mar 03 '11 at 14:52

Edmhs

3,645
27
39

8

Maybe I'm missing something, but with DATETIME types, the CURDATE() solution doesn't work. This does. – James John McGuire 'Jahmic' Oct 18 '13 at 08:42
plus this takes a lot of time as there is overhead of converting DATETIME to date via the DATE() function and then comparing with the where condition. – roopunk Aug 14 '14 at 09:48
1

Do not use functions on columns, it makes the query to ignore index, causing slow queries. Check my answer for alternative approach https://stackoverflow.com/a/42365426/4311336 – Abdul Rehman Aug 26 '19 at 13:00
"equal or greater than today" - how does your answer resolve that? How could it return any row where `myDate` is in the future? – Nico Haase Jun 21 '23 at 12:36

score 46 · Answer 3 · answered Mar 03 '11 at 14:52

46

SELECT * FROM users WHERE created >= NOW();

if the column is datetime type.

answered Mar 03 '11 at 14:52

Shakti Singh

84,385
21
134
153

1

i mean today not now :D i need today like 2011-03-03 not 2011-03-03 14:02:02 – n00b Mar 03 '11 at 14:54
@n00b: it will returns rows greater than today don't worry if the time is included – Shakti Singh Mar 03 '11 at 14:57
4

lets say it's 2011-02-02 14:02:02 - users that have been created at 10:02:02 wouldn't be returned in your version even though they were created "today" :) – n00b Mar 03 '11 at 15:02
@n00b: Lol ya, no users would ever be returned, if only we could stop time. – Mike Purcell Dec 07 '12 at 00:52

score 28 · Answer 4 · edited Mar 14 '18 at 03:10

28

Answer marked is misleading. The question stated is DateTime, but stated what was needed was just CURDATE().

The shortest and correct answer to this is:

SELECT * FROM users WHERE created >= CURRENT_TIMESTAMP;

edited Mar 14 '18 at 03:10

Stephen Rauch

47,830
31
106
135

answered Mar 14 '18 at 02:48

JeffMan

412
4
11

score 18 · Answer 5 · answered May 09 '13 at 08:29

18

If 'created' is datetime type

SELECT * FROM users WHERE created < DATE_ADD(CURDATE(), INTERVAL 1 DAY);

CURDATE() means also '2013-05-09 00:00:00'

answered May 09 '13 at 08:29

bart

321
2
9

Thanks for this. I actually needed DATE_SUB (used in the same way), but this put me on the right path. – Daniel Price Nov 05 '15 at 12:29
This will not return data where date contains 0s in time, i.e '2013-05-09 00:00:00' will be missed out – Abdul Rehman Aug 26 '19 at 13:30
"equal or greater than today" - how does your answer resolve that? How could it return any row where `myDate` is in the future? – Nico Haase Jun 21 '23 at 12:37

Abdul Rehman · Answer 6 · 2021-01-06T12:01:27.007

If the column have index and a function is applied on the column then index doesn't work and full table scan occurs, causing really slow query.

Bad Query; This would ignore index on the column date_time

select * from users
where Date(date_time) > '2010-10-10'

To utilize index on column created of type datetime comparing with today/current date, the following method can be used.

Solution for OP:

select * from users
where created > CONCAT(CURDATE(), ' 23:59:59')

Sample to get data for today:

select * from users
where 
    created >= CONCAT(CURDATE(), ' 00:00:00') AND
    created <= CONCAT(CURDATE(), ' 23:59:59')

Or use BETWEEN for short

select * from users 
where created BETWEEN 
      CONCAT(CURDATE(), ' 00:00:00') AND CONCAT(CURDATE(), ' 23:59:59')

Tip: If you have to do a lot of calculation or queries on dates as well as time, then it's very useful to save date and time in separate columns. (Divide & Conquer)

score 3 · Answer 7 · edited May 02 '11 at 15:25

3

SELECT * FROM users WHERE created >= now()

edited May 02 '11 at 15:25

kapa

77,694
21
158
175

answered Mar 03 '11 at 14:52

Silx

2,663
20
21

This won't return the records before this moment from today. – gsziszi Mar 15 '20 at 09:55

score 1 · Answer 8 · edited May 20 '16 at 12:50

1

The below code worked for me.

declare @Today date

Set @Today=getdate() --date will equal today    

Select *

FROM table_name
WHERE created <= @Today

edited May 20 '16 at 12:50

slavoo

5,798
64
37
39

answered May 20 '16 at 12:39

Rob Fahndrich

11
1

score -2 · Answer 9 · edited Nov 18 '14 at 10:32

-2

SELECT * FROM table_name WHERE CONCAT( SUBSTRING(json_date, 11, 4 ) ,  '-', SUBSTRING( json_date, 7, 2 ) ,  '-', SUBSTRING(json_date, 3, 2 ) ) >= NOW();

json_date ["05/11/2011"]

edited Nov 18 '14 at 10:32

Sagar Pudi

4,634
3
32
51

answered Nov 18 '14 at 10:12

vpgodara

329
2
2

This is a very raw solution – IgniteCoders May 26 '15 at 09:07
Please add some explanation to your answer such that others can learn from it. Where does `json_date` come from? – Nico Haase Jun 21 '23 at 12:37

score -6 · Answer 10 · edited Aug 09 '16 at 07:11

-6

you can return all rows and than use php datediff function inside an if statement, although that will put extra load on the server.

if(dateDiff(date("Y/m/d"), $row['date']) <=0 ){    
}else{    
echo " info here";    
}

edited Aug 09 '16 at 07:11

Pathik Vejani

4,263
8
57
98

answered Oct 20 '14 at 21:59

Koney L Visinko

1
1

5

this is very slow compared to the database select – IgniteCoders May 26 '15 at 09:10
This won't work if the OP won't use PHP after all – Nico Haase Jun 21 '23 at 12:38

Datetime equal or greater than today in MySQL

10 Answers10

Linked

Related