CODEWITHSUNDEEP
phpmysqldatetime
Vishist Varugeese
Vishist Varugeese

Reputation: 1580

How to get data from a specific date in sql

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

    $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 retrieve the data of the current date (today's date).

I tried using this query,

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

But this results in zero rows selected.

Please help me solve this issue.

Upvotes: 8

Views: 2047

Answers (2)

Kiwagi
Kiwagi

Reputation: 178

this will also work

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One option would be to wrap created_at with DATE(), and then compare to CURDATE():

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

But this has the drawback of precluding the possibility of using an index on the created_at column. We could also phrase this as follows:

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

This would allow an index to be used on created_at, though it is a bit more verbose.

Upvotes: 10

Related Questions