CODEWITHSUNDEEP
mysqlsqldatetime
n00b
n00b

Reputation: 16566

Datetime equal or greater than today in MySQL

What's the best way to do following:

SELECT * FROM users WHERE created >= today;

Note: created is a datetime field.

Upvotes: 228

Views: 794277

Answers (11)

a55
a55

Reputation: 565

I have an old time like:

$old = time()-600;

To get only messages in last 10 minutes

mysqli_query("SELECT * FROM tbl WHERE UNIX_TIMESTAMP(created) > $old ;");

may useful for anyone

Upvotes: 0

Edmhs
Edmhs

Reputation: 3731

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

Read more: https://www.tomjepson.co.uk/mysql-select-from-table-where-date-today

Upvotes: 95

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125524

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.

Upvotes: 409

Abdul Rehman
Abdul Rehman

Reputation: 1794

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)

Upvotes: 15

JeffMan
JeffMan

Reputation: 492

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;

Upvotes: 31

Koney L Visinko
Koney L Visinko

Reputation: 1

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";    
}

Upvotes: -6

Rob Fahndrich
Rob Fahndrich

Reputation: 11

The below code worked for me.

declare @Today date

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

Select *

FROM table_name
WHERE created <= @Today

Upvotes: 1

vpgodara
vpgodara

Reputation: 338

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"]

Upvotes: -2

bart
bart

Reputation: 321

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'

Upvotes: 18

Silx
Silx

Reputation: 2701

SELECT * FROM users WHERE created >= now()

Upvotes: 3

Shakti Singh
Shakti Singh

Reputation: 86476

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

if the column is datetime type.

Upvotes: 49

Related Questions