How to check if a specific time is between to dates?-CodePudding

I want to create a function that checks, if a specific time is between two timestamps that are stored in a MySQL database.

The function should be able to ignore the time ($fullday = true) or check the time as well.

My presence table has a row with start = 2021-11-01 10:00:00 and end = 2021-11-05 18:00:00.

is_available('2021-11-02', true); should give me a result, but is doesn't.

What I expect as well:

is_available('2021-11-01 09:30:00'); should not have a result, as 9:30 is before 10:00 but is_available('2021-11-01 10:01:00'); should give me a result. And so on.

The query, my function creates is:

SELECT *
FROM `presence`
WHERE DATE_FORMAT(start, "%Y-%m-%d") <= "2021-11-02"
AND DATE_FORMAT(end, "%Y-%m-%d") >= "2021-11-02"

And this my function so far:

function is_available($date, $fullday = false)
{
    $presenceModel = new PresenceModel();

    if ($fullday) {
        $date = date('Y-m-d', strtotime($date));
        $presences = $presenceModel
            ->where('DATE_FORMAT(start, "%Y-%m-%d") <= "' . $date . '"')
            ->where('DATE_FORMAT(end, "%Y-%m-%d") >= "' . $date . '"')
            ->findAll();
    } else {
        $date = date('Y-m-d H:i:s', strtotime($date));
        $presences = $presenceModel
            ->where('DATE_FORMAT(start, "%Y-%m-%d %H:%i:%s") <= "' . $date . '"')
            ->where('DATE_FORMAT(end, "%Y-%m-%d %H:%i:%s") >= "' . $date . '"')
            ->findAll();
    }

    return count($presences) > 0 ? true : false;
}

CodePudding user response：

My approach would be to use strtotime() function to convert the date into an int and then convert the other date from the db and then compare those ints in php.

if($datenow > $dbstartdate && $datenow < $dbenddate){
    return true;
}else{
    return false;
}

CodePudding user response：

You should be able to simplify this dramatically by getting MySQl to do more of the work. You don't need to format DATETIME columns to do comparisons on them, and BETWEEN will further reduce your PHP effort.

Based on your code above I think this will do the job:

function is_available($date, $fullDay = false) {
    $presenceModel = new PresenceModel();
    $date = date('Y-m-d H:i:s', strtotime($date));

    if ($fullDay) {
        $presences = $presenceModel
            ->where("'$date' between DATE(`start`) and DATE(`end`)" )
            ->findAll();
    } else {
        $presences = $presenceModel
            ->where("'$date' between `start` and `end`" )
            ->findAll();
    }
    return count($presences) > 0 ? true : false;
}

However, using SELECT * will return the entirety of all the matching rows, when all you really need is whether one exists. You should aim to get your query something close to this, which will return just a single value (1) if there's a matching row.:

SELECT 1 FROM `presence`
  WHERE "2021-11-02" between start AND end 
  LIMIT 1