Mysql Split By Time Range

I have a view which has two columns as below: id hour_from hour_to 1 12:00 20:00 2 09:00 13:00 3 07:30 19:00 .................... I'm trying

Solution 1:

You need a rowsource with all the times you want returned. Then you can use a JOIN operation to select the rows to be returned.

If you had a table like this:

CREATETABLE cal (tm TIMENOTNULLPRIMARY KEY) ENGINE=InnoDB ;
INSERTINTO cal (tm)  VALUES ('00:00:00'),('00:30:00');
INSERTINTO cal (tm) SELECT ADDTIME(tm,'01:00:00') FROM cal;
INSERTINTO cal (tm) SELECT ADDTIME(tm,'02:00:00') FROM cal;
INSERTINTO cal (tm) SELECT ADDTIME(tm,'04:00:00') FROM cal;
... 

Then you could use that in your query:

SELECT v.id
     , c.tm
  FROM myview v
  JOIN cal c
    ON c.tm >= v.hour_from
   AND c.tm <= v.hour_to
 ORDERBY v.id, c.tm

If you want the time values in a specific format, you can use the DATE_FORMAT function:

SELECT v.id
     , DATE_FORMAT(c.tm,'%H:%i') AS hour_

This assumes that the values returned by the view are TIME datatype. If they aren't, you'll want to convert them. (Or you could get the same result working with character strings in a canonical format.)

NOTE: It's not mandatory that you have a table; it can be any row source, such as an inline view. You just need enough rows (in your case, assuming half hour increments from 00:00 thru 23:30, thats 48 rows.)

If you have any time ranges that "cross" a midnight boundary (e.g. 22:00 to 02:00), the example query won't return any rows. You'd need to make adjustments.

Something like:

 JOIN cal c
   ON( t.hour_from <= t.hour_to 
      AND c.tm >= v.hour_from
      AND c.tm <= v.hour_to
      )
   OR ( t.hour_from > t.hour_to 
      AND(   c.tm <= v.hour_from
          OR  c.tm >= v.hour_to
          )
      )