PHP / MySQL在UNIX时间范围内查找事件

Question

i'm hoping to get some help here with a problem I have been struggling with for a long time.

I'm having a MySQL database with events where the starting time of the event is stored in UNIX time. I need to loop thru the next 7 days of the week (today included), from 5AM to 4:59AM to find event that begins within that timespan.

That means, that i need to find all events witch begins from 1418270400 to 1418356799, and then add 1 day to loop to both dates (and not 86400 seconds).

I have been struggling with this for some time, and simply can't get my head around this... Is there a kind person with some more skills with PHP who please could help me?

This is the code i have made to calculate the time periods:

$timestamp = time();
$dtNow = new DateTime();
$dtNow->setTimestamp($timestamp);

$beginOfDay = clone $dtNow;

$beginOfDay->setTime(05, 00, 0);

$endOfDay = clone $beginOfDay;
$endOfDay2 = clone $beginOfDay;
$endOfDay->setTime('05, 00, 0');
$endOfDay2->setTime('05, 00, 0');
$endOfDay->add(new DateInterval('P1D'));
$endOfDay2->add(new DateInterval('P7D'));
$endOfDay->modify('1 second ago');
$endOfDay2->modify('1 second ago');

I need to get all events from a MySQL database, that has the timestamp between $beginOfDay and $endOfDay, but are lower then $endOfDay2 ...

Answer 1

foreach ($days as $day) {
    $date = new DateTime();
    $date->setTimestamp($starting_timestamp); // OR
    $date->setTime(5);

    $date2 = new DateTime();
    $date2->setTimestamp($ending_timestamp); // OR
    $date2->setTime(4, 59);

    $query = "SELECT event.* from events WHERE timestamp BETWEEN($date->getTimestamp(), $date2->getTimestamp())";
// query stuff or what ever you want to do.
}

Just a simple example of something that might work for you, hopefully this helps.