find all records that are within the same day of another unix timestamp mysql

Question

Let's say I have a unix timestamp of 1497664242 which is Saturday, June 17, 2017 1:50:42 AM

I want to find all the records in the table that have a timestamp within that same day.

I tried setting a timestamp like 1494979200 which is Saturday, June 17, 2017 12:00:00 AM thinking I can use a general time for that day and pull all the records that match that same day.

I am trying to loop from a specific date and find all the records that match that day then increment up 1 day until today 04/12/2019

$start_date = "05/17/2017";

while ($start_date != "04/12/2019") {
    $timestamp = strtotime($start_date);
    $sql_select = "SELECT * FROM table WHERE DATE(timestamp) = DATE(FROM_UNIXTIME($timestamp))";
    $result_select = $GLOBALS['db']->query($sql_select);
    while ($row = $result_select->fetch_array()) {
        // will do stuff here
    }
    $start_date = date("m/d/Y", strtotime($start_date. "+1 day"));
}

Answer 1

If you convert your timestamp to a DATE i.e. 2017-06-17 and also the timestamp field on your database you should get any row in that day

Now we know that the column timestamp is in fact defined as a VARCHAR() you would have to do this

SELECT * 
FROM table 
WHERE DATE(FROM_UNIXTIME(timestamp)) = '$start_date';

So for your actual code

$start_date = "2017-05-17";

while ($start_date != "2019-04-12") {

    $sql_select = "SELECT * 
                    FROM table 
                    WHERE DATE(FROM_UNIXTIME(timestamp)) = '$start_date'";

    $result_select = $GLOBALS['db']->query($sql_select);
    while ($row = $result_select->fetch_array()) {
        // will do stuff here
    }
    $start_date = date("Y-m-d", strtotime($start_date. "+1 day"));
}