Solved

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

Posted on 2016-09-12
4
75 Views
Last Modified: 2016-09-14
Hello Experts,

I using PHP/MySQL have the following mysql config table, with the following fields and values - where the values can change periodically;

Column            Value            Desc

Timeslot            20                    minutes

MonOpen      14:00            time open in 24 hour notation
MonClosed      18:00            time closed in 24 hour notation

TueOpen      08:00            time open in 24 hour notation
TueClosed      18:00            time closed in 24 hour notation

WedOpen      08:00            time open in 24 hour notation
WedClosed      18:00            time closed in 24 hour notation

ThuOpen      08:00            time open in 24 hour notation
ThuClosed      19:00            time closed in 24 hour notation

FruOpen            06:20            time open in 24 hour notation
FriClosed      20:00            time closed in 24 hour notation

SatOpen            06:00            time open in 24 hour notation
SatClosed      13:00            time closed in 24 hour notation

SunOpen      00:00            time open in 24 hour notation
SunClosed      00:00            time closed in 24 hour notation

For any date range, I currently create a static table for all available xx mins timeslots.

My question is, given a range of dates, is it possible to create a MySQL view instead as this would stop the table growing over a period of time.

Many thanks

Regards

Jamie
0
Comment
Question by:Jamie
  • 2
  • 2
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41796176
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";
}

Open 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

Open in new window

0
 

Author Comment

by:Jamie
ID: 41797300
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
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41797674
Hi, Jamie.  This design sounds like there is a backstory to the application, and it might be better to look at the design from the "50,000 foot view."  The days of the year and the hours of the day are highly predictable and susceptible to generalized programming (even to include DST, holidays, shipping delays, etc) so my instinct is to look for a way to "ask a question" of an API instead of storing all of this information, slot-by-slot in a database.

Some more information is available in this article.  It sounds like you might be looking for a resource scheduling calendar  (search the article for that text).
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

Maybe if you can tell us what you do with this information we can suggest some simpler alternatives.

Also, a table of 11,000 entries, or even 99,000 entries is almost nothing to modern DB servers, if it's correctly indexed!
0
 

Author Closing Comment

by:Jamie
ID: 41799160
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now