CODEWITHSUNDEEP
phpmysqlunix-timestamp
Cesar Bielich
Cesar Bielich

Reputation: 4945

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

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"));
}

Upvotes: 1

Views: 2175

Answers (2)

fifonik
fifonik

Reputation: 1606

I would NOT use functions in SQL query as suggested (DATE(FROM_UNIXTIME(timestamp))) as this will prevent SQL to use index on timestamp column.

I'd calculate two datetimes in php (day's begin + end), something like this:

$dt       = new DateTime('@' . $timestamp); // Should take care about timezones if you need
$dt_begin = $dt->format('Y-m-d 00:00:00');
$dt_end   = $dt->format('Y-m-d 23:59:59');

If your timestamps in DB stored as integers:

SELECT
    * 
FROM
    table 
WHERE
    timestamp BETWEEN UNIX_TIMESTAMP('$dt_begin') AND UNIX_TIMESTAMP('$dt_end')

If your timestamps in DB are stored as TIMESTAMP or DATETIME:

SELECT
    * 
FROM
    table 
WHERE
    timestamp BETWEEN '$dt_begin' AND '$dt_end'

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94642

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"));
}

Upvotes: 1

Related Questions