Retrieve only today records from MySQL database using PHP

Question

In my database I stored date and time as a TIMESTAMP values. I want to retrive only today data from the database. This is code that I tried

$today = date("Y-m-d") . '00:00:00';
$last = date("Y-m-d") . '23:59:59';


$sql = "SELECT id, name, name2,some,some,submittimestamp,some FROM recs where submittimestamp Between $today AND $last";
$result = $conn->query($sql);

But I only get the 0 results message using this. There is more than 100 rows added today.

How can I solve this ? I cant change the database now. only way to solve this is change the PHP script

Do I need to convert PHP datetime to MYSQL timestamp ?

This is my sample database entry time-stamp value

2014-10-02 15:47:01

I only want to retrieve data for one day. time is not required !!

Maybe it's because you forgot the single space here $today = date("Y-m-d") . ' 00:00:00'; instead of $today = date("Y-m-d") . '00:00:00';, Ditto for $last. Otherwise, that seems OK to me — smarber
– smarber, Commented May 4, 2015 at 15:38
Actually I don't think the problem has to do with PHP Datetime, What I'd do is to take the SQL query you built and I'd try it into a mysql console, if the result is still 0, then you can say that problem comes from PHP Datetime conversion or whatever :) — smarber
– smarber, Commented May 4, 2015 at 15:44

mysqlrockstar · Accepted Answer · 2015-05-04 15:40:25Z

5

SELECT id, name, name2, submittimestamp 
FROM recs WHERE submittimestamp  > DATE_SUB(CURDATE(), INTERVAL 1 DAY);

Also you may try this, as DATE() ignores time part

SELECT id, name, name2, submittimestamp
FROM recs WHERE DATE(submittimestamp) = CURDATE();

answered May 4, 2015 at 15:40

mysqlrockstar

2,6301 gold badge21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shiv pratap Over a year ago

hi kaka dba ...this query only show today result ??

Collectives™ on Stack Overflow

Retrieve only today records from MySQL database using PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related