Create MySQL view instead of static table for xx minute timeslots within a given date period

I think I would not put this information into a database.  Or if I did, I might extract it and put it into an object like this.  You might store the open/close times and the timeslot duration, not sure exactly how you would want to use those values, so there is a setter method on the object.
https://iconoun.com/demo/temp_jamwales.php

<?php // demo/temp_jamwales.php
/**
 * https://www.experts-exchange.com/questions/28969381/Create-MySQL-view-instead-of-static-table-for-xx-minute-timeslots-within-a-given-date-period.html
 *
 * https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
 * https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
 *
 * http://php.net/manual/en/book.datetime.php
 * http://php.net/manual/en/class.datetime.php
 * http://php.net/manual/en/class.dateinterval.php
 *
 */
error_reporting(E_ALL);
echo '<pre>';


Class Day
{
    public $alpha, $omega;
    public function __construct($alpha, $omega)
    {
        $this->alpha = new DateTime('Jan 1, 2001 ' . $alpha);
        $this->omega = new DateTime('Jan 1, 2001 ' . $omega);
    }
}

Class WorkHours
{
    protected $days = [];
    public function __construct($timeslot=20)
    {
        $this->timeslot = $timeslot;
        $this->days['Mon'] = new Day('14:00', '18:00');
        $this->days['Tue'] = new Day('08:00', '18:00');
        $this->days['Wed'] = new Day('08:00', '18:00');
        $this->days['Thu'] = new Day('08:00', '19:00');
        $this->days['Fri'] = new Day('06:20', '20:00');
        $this->days['Sat'] = new Day('06:00', '13:00');
        $this->days['Sun'] = new Day('00:00', '00:00');
    }
    public function setHours($day, $open, $close)
    {
        $this->days[$day] = new Day($open, $close);
    }
    public function getHours($day)
    {
        $hours = $this->days[$day];
        $open  = $hours->alpha->format('g:ia');
        $close = $hours->omega->format('g:ia');
        return [ 'open' => $open, 'close' => $close ];
    }
    public function isOpen($datetime)
    {
        $obj = new DateTime($datetime);
        $day = $obj->format('D');
        $time = $obj->format('H:i');
        $test = new DateTime('Jan 1, 2001 ' . $time);
        if ( ($test >= $this->days[$day]->alpha) && ($test < $this->days[$day]->omega) )
        {
            // COMPUTE THE TIMESLOT
            $diff = $this->days[$day]->alpha->diff($test);
            $hour = $diff->h;
            $mins = $diff->i;
            $appt = $hour * 60 + $mins;
            $slot = (int)floor($appt / $this->timeslot) + 1;
            return $slot;
        }
        return FALSE;
    }
}


$tests =array
( 'Now'
, 'Now + 20 minutes'
, 'Monday 2:00pm'
, 'Monday 2:10pm'
, 'Monday 2:20pm'
, 'Monday 2:30pm'
, 'Monday 1:30pm'
, 'Friday 7:59pm'
, 'Friday 8:00pm'
, 'Friday 8:01pm'
, 'Sunday'
)
;


// SHOW HOW TO USE THE OBJECT
$wh = new WorkHours();


// SHOW HOW TO GET THE WORK HOURS
$test = 'Fri';
echo PHP_EOL . "WORK HOURS ON $test: ";
print_r( $wh->getHours($test) );


// SHOW HOW TO GET THE SLOT FOR A PARTICULAR DATE/TIME VALUE
foreach ($tests as $test)
{
    $res = $wh->isOpen($test);
    if ($res === FALSE)
    {
        echo PHP_EOL . "$test IS OUTSIDE THE WORK HOURS";
    }
    else echo PHP_EOL . "$test IS IN SLOT: $res";
}

Select allOpen in new window

Outputs something like:

WORK HOURS ON Fri: Array
(
    [open] => 6:20am
    [close] => 8:00pm
)

Now IS IN SLOT: 23
Now + 20 minutes IS IN SLOT: 24
Monday 2:00pm IS IN SLOT: 1
Monday 2:10pm IS IN SLOT: 1
Monday 2:20pm IS IN SLOT: 2
Monday 2:30pm IS IN SLOT: 2
Monday 1:30pm IS OUTSIDE THE WORK HOURS
Friday 7:59pm IS IN SLOT: 41
Friday 8:00pm IS OUTSIDE THE WORK HOURS
Friday 8:01pm IS OUTSIDE THE WORK HOURS
Sunday IS OUTSIDE THE WORK HOURS

Select allOpen in new window

Hello Ray,

Apologies for the delay in getting back to you.

Many thanks for looking at this and the great solution you have suggested and provided in demo form. Whilst I can see where I would use this, I do need access to the timeslots directly from MySQL as I have other database tables which need to cross reference the static 'timeslots' table - which is ideally why I need it as view instead;

e.g for Monday 19th September to Tuesday 20th September, the 'timeslots' view would contain;

19/09/2016 14:00:00
19/09/2016 14:20:00
19/09/2016 14:40:00
19/09/2016 15:00:00
19/09/2016 15:20:00
19/09/2016 15:40:00
19/09/2016 16:00:00
19/09/2016 16:20:00
19/09/2016 16:40:00
19/09/2016 17:00:00
19/09/2016 17:20:00
19/09/2016 17:40:00
19/09/2016 18:00:00
20/09/2016 08:00:00
20/09/2016 08:20:00
20/09/2016 08:40:00
20/09/2016 09:00:00
20/09/2016 09:20:00
20/09/2016 09:40:00
20/09/2016 10:00:00
20/09/2016 10:20:00
20/09/2016 10:40:00
20/09/2016 11:00:00
20/09/2016 11:20:00
20/09/2016 11:40:00
20/09/2016 12:00:00
20/09/2016 12:20:00
20/09/2016 12:40:00
20/09/2016 13:00:00
20/09/2016 13:20:00
20/09/2016 13:40:00
20/09/2016 14:00:00
20/09/2016 14:20:00
20/09/2016 14:40:00
20/09/2016 15:00:00
20/09/2016 15:20:00
20/09/2016 15:40:00
20/09/2016 16:00:00
20/09/2016 16:20:00
20/09/2016 16:40:00
20/09/2016 17:00:00
20/09/2016 17:20:00
20/09/2016 17:40:00
20/09/2016 18:00:00

As a static table, 1 year of 20 min slots is around 11,000 rows, which would grow by that amount each year. I got a couple of ideas on how to manage the static table through code, but I thought, a view, if possible, would crack this nut in one action.

Many thanks

Regards

Jamie

Hi Ray,

Many thanks for all your help with this, great advice and article, It has been invaluable, given me the solution and shown other possibilities for my future coding needs - very much appreciated.

Best Regards

Jamie