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.

If you found duplicate then give me detail of original question. Thanks! — Parixit, Aug 27 '13 at 04:49
this is a link to convert all convert datetime to upper or lower 15 mins interval http://stackoverflow.com/a/3473286/2605821 — plain jane, Aug 27 '13 at 05:10
Thanks! first problem resolved. Now I want to split it in interval and store in array. — Parixit, Aug 27 '13 at 05:14
In first step I want to convert start and end time to upper/lower level quarter hour. Now, I want to make array which store 15 min interval values between that start and end time. — Parixit, Aug 27 '13 at 05:23

score 9 · Answer 1 · answered Aug 27 '13 at 05:26

9

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);

answered Aug 27 '13 at 05:26

Sutandiono

1,748
1
12
21

is it possible with mysql? – Parixit Aug 27 '13 at 05:34
both php/mysql. But mysql preferable – Parixit Aug 27 '13 at 05:48
1

I can't think of a straight way of doing it via MySQL. The indirect way of doing it would be to create a table which contains all the 15-minute intervals in 24 hours, and then you can do `SELECT fifteen_mins FROM fifteen_mins_table WHERE fifteen_mins >= '2013-08-28 1:45:00' AND fifteen_mins <= '2013-08-28 22:15:00'`. – Sutandiono Aug 27 '13 at 05:58
1

Otherwise, see something similar [posted here](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) using temporary table and stored procedure. – Sutandiono Aug 27 '13 at 06:00
Exactly you need to do it in PHP and store it in table – plain jane Aug 27 '13 at 06:01

score 2 · Accepted Answer · answered Feb 01 '14 at 08:54

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
}

score 2 · Answer 3 · answered Feb 01 '14 at 19:17

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

CREATE FUNCTION ToUpper15(dateParam DATETIME)
CREATE FUNCTION ToLower15(dateParam DATETIME)
CREATE PROCEDURE AddIntervalMinutes(startdate DATETIME, enddate DATETIME, interval INT)

Here is an example of some pseudo MySQL Code, I have never programmed MySQL, I tried to look for the documentation and its terrible, otherwise this is really a 30 minute Task, if you can find documentation of the Syntax of the Language.

Keep in mind

When you round up above 45 minutes you are rounding to the next hour
Do the same with ToLower15
Use system function AddTime() to do the 15 minute interval

Below is how you might do this

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

split time interval in 15 min slot using mysql

3 Answers3