Split time slot in 15 minute slot using mysql

Question

I have 2 dates

start datetime = 2013-08-28 17:43:41 And end datetime = 2013-08-28 22:23:51

Now I want to convert the start time to the upper level with a 15-minute interval, for example, 2013-08-28 17:45:00 in this case, in the same way for the final time, converted to the lower level of the 15-minute interval, for example 2013-08-28 22:15:00

Then after I want an array with an interval of 15 minutes in between.

eg. for our case it should be

  a [0] = 2013-08-28 17:45:00
 a [0] = 2013-08-28 18:00:00
 a [1] = 2013-08-28 18:15:00
 a [2] = 2013-08-28 18:30:00
 a [3] = 2013-08-28 18:45:00
 a [4] = 2013-08-28 19:00:00
 ...... like wise 

I want this to use mySql / php, but mysql is priority because the data comes from my database.

Answer 1

I solved this using PHP and requested to get the start and end of datetime from the database.

 $start_datetime = strtotime($start_datetime); //from 2013-08-28 17:43:41 to timestamp $end_datetime = strtotime("+$duration minutes", $start_datetime); //from 2013-08-28 17:43:41 to timestamp //loop till start time is less than end time while ($start_datetime < $end_datetime) { //add date as a key in first level array if(!array_key_exists(date('Ym-d', $start_datetime), $booking_slot)) { $booking_slot[date('Ym-d', $start_datetime)]=array(); } //add time slot for perticular date array array_push($booking_slot[date('Ym-d', $start_datetime)], date("h:i A", $start_datetime)); //add $interval to find next interval $start_datetime = strtotime("+$interval minutes", $start_datetime); //slot or interval } 

Answer 2

If you have a start and end time, convert them to a UNIX timestamp. After that, just create a loop that will add 15 minutes to the beginning and continue working until you reach the end time.

Something like that:

 $array_of_time = array (); $start_time = strtotime ("2013-08-28 17:45:00"); $end_time = strtotime ("2013-08-28 22:15:00"); $fifteen_mins = 15 * 60; while ($start_time <= $end_time) { $array_of_time[] = date ("Ymd H:i:s", $start_time); $start_time += $fifteen_mins; } print_r ($array_of_time); 

Answer 3

You can create a custom MySQL function and procedures with the following signatures.

• CREATE FUNCTION ToUpper15 (dateParam DATETIME)
• CREATE FUNCTION ToLower15 (dateParam DATETIME)
• CREATION PROCEDURE AddIntervalMinutes (startdate DATETIME, enddate DATETIME, int interval)

Here is an example of some pseudo MySQL code, I never programmed MySQL, I tried to look for documentation and its terrible, otherwise it is really a 30-minute task if you can find the language syntax documentation.

Keep in mind

• When you round for more than 45 minutes, you round to the next hour.
• Do the same with ToLower15
• Use the AddTime () system function to execute a 15 minute interval

Below you can do it

 CREATE FUNCTION ToUpper15(dateParam DATETIME) RETURNS DATETIME DETERMINISTIC BEGIN DECLARE dateYear INT; DECLARE dateMonth INT; DECLARE dateDay INT; DECLARE dateHour INT; DECLARE dateMinute INT; SET dateYear = YEAR(dateParam); SET dateMonth = MONTH(dateParam); SET dateDay = DAY(dateParam); SET dateHour = HOUR(dateParam); SET dateMinute = MINUTE(dateParam); IF (dateMinute >= 0 AND dateMinute < 15) THEN SET dateMinute = 15; ELSEIF (dateMinute >= 15 AND dateMinute < 30) THEN SET dateMinute = 30; ELSEIF (dateMinute >= 30 AND dateMinute < 45) THEN SET dateMinute = 45; ELSEIF (dateMinute >= 45 AND dateMinute < 60) THEN BEGIN SET dateMinute = 0; SET dateHour = dateHour + 1; END END IF; RETURN CONCAT(dateYear, '-', dateMonth, '-', dateDay, ' ', dateHour, ':', 'dateMinute'); END