split time interval in 15 min 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 start time to upper level 15 min interval like 2013-08-28 17:45:00 in this case, same way for end time converted in lower level 15 min interval like 2013-08-28 22:15:00

Then after I want array of 15 min interval of difference between that time.

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 using mySql/php, but mysql is priority because data came from my database.

Answer 1

If you have the start time and end time, convert them to UNIX timestamp. After that, simply create a loop that adds 15 minutes to the start time and keep going until you reach the end time.

Something like this:

$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 ("Y-m-d H:i:s", $start_time);
   $start_time += $fifteen_mins;
}

print_r ($array_of_time);

Answer 2

I had solved this by PHP and query to retrieve start and end datetime from 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('Y-m-d', $start_datetime), $booking_slot)) {
          $booking_slot[date('Y-m-d', $start_datetime)]=array();
     }

     //add time slot for perticular date's array
     array_push($booking_slot[date('Y-m-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
}