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)
SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())
Read more: http://www.tomjepson.co.uk/tutorials/36/mysql-select-where-date-today.html
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'
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;
The below code worked for me.
declare @Today date
Set @Today=getdate() --date will equal today
Select *
FROM table_name
WHERE created <= @Today
SELECT * FROM users WHERE created >= now()
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";
}
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"]
SELECT * FROM users WHERE created >= NOW();
if the column is datetime type.
Source: Stackoverflow.com