Could you point a MySQL Server query that returns the number of business hours between 02 business dates considering hollydays?

<?php // demo/temp_eduardo.php
/**
 * https://www.experts-exchange.com/questions/28993552/Could-you-point-a-MySQL-Server-query-that-returns-the-number-of-business-hours-between-02-business-dates-considering-hollydays.html
 * https://www.experts-exchange.com/questions/28989520/Could-you-point-a-MySQL-Server-query-that-returns-the-number-of-business-days-between-02-dates.html
 *
 * https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
 * https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
 */
error_reporting(E_ALL);


// A FUNCTION TO COUNT BUSINESS DAYS BETWEEN GIVEN DATES
function count_business_days($alpha="today", $omega="tomorrow")
{
    // CREATE YOUR ARRAY OF HOLIDAYS, OR LOAD IT FROM AN EXTERNAL TABLE
    $holidays = array();
    $november = strtotime(date('Y') . '-11-0');
    $january  = strtotime(date('Y') . '-01-0');
    $nextyear = mktime(0,0,0, 1, 1, date('Y') + 1);
    $holidays['Dr_M_L_King']  = date('r', strtotime('Third Monday', $january));
    $holidays['Independence'] = date('r', strtotime(date('Y') . '-07-04'));
    $holidays['Thanksgiving'] = date('r', strtotime('Fourth Thursday', $november));
    $holidays['Christmas']    = date('r', strtotime(date('Y') . '-12-25'));
    $holidays['NewYear']      = date('r', $nextyear);

    // ACTIVATE THIS TO SEE THE HOLIDAYS
    // print_r($holidays);

    // INTERPRET THE INPUTS INTO TIMESTAMPS
    if (!$alpha = strtotime($alpha)) return FALSE;
    if (!$omega = strtotime("$omega")) return FALSE;

    // MAKE AN ARRAY OF FUTURE TIMESTAMPS AND RFC2822 DATES
    $arr = range($alpha, $omega, 86400);
    $arr = array_flip($arr);
    foreach ($arr as $timestamp_key => $null)
    {
        // ASSIGN RFC2822 DATE STRINGS TO EACH TIMESTAMP
        $arr[$timestamp_key] = date('r', $timestamp_key);

        // REMOVE THE DAY FROM THE ARRAY IF IT IS A HOLIDAY OR WEEKEND DAY
        if (in_array($arr[$timestamp_key], $holidays)) $arr[$timestamp_key] = 'S';
        if (substr($arr[$timestamp_key],0,1) == 'S') unset($arr[$timestamp_key]);
    }

    // ACTIVATE THIS TO SEE THE ARRAY OF BUSINESS DAYS
    // print_r($arr);

    // RETURN THE COUNT OF BUSINESS DAYS
    return count($arr);
}


// DEMONSTRATE THE FUNCTION
$a = 'Nov 15, 2016';
$z = 'Dec 27, 2016';
$n = count_business_days($a, $z);

// COMPUTE THE HOURS
$h = $n * 8;
echo PHP_EOL . "BETWEEN $a AND $z THERE ARE $n BUSINESS DAYS, THEREFORE $h BUSINESS HOURS";

Select allOpen in new window