How to get data from a specific date in sql

Question

I am trying to get current date data in MySQL. I have a column called created_at that stores the date and time (2017-12-31 11:32:54) using the NOW() function using instructions,

  $stmt = $this->conn->prepare("INSERT INTO log(id, name, created_at) VALUES(?, ?, NOW())"); $stmt->bind_param("ss", $id, $name); $result = $stmt->execute(); 

Now I want to ignore the time in the created_at column and get the current date data (today's date).

I tried using this query,

 SELECT * FROM log WHERE created_at = DATE_SUB(CURDATE(), INTERVAL 0 DAY) 

But this leads to zero rows selected .

Please help me solve this problem.

Answer 1

One option is to wrap created_at with DATE() and then compare with CURDATE() :

 SELECT * FROM log WHERE DATE(created_at) = CURDATE(); 

But this has a drawback that precludes the possibility of using the index in the created_at column. We could also state it as follows:

 SELECT * FROM log WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY; 

This will allow you to use the index on created_at , although it is a bit more verbose.

Answer 2

---

it will also work

 SELECT * FROM log WHERE DATE(created_at, '%Y-%m-%d') = CURDATE();