gistfile1.sql

-- Query 1: Use sensiblised day and time, also check if rollover is required.
SET @TIME=DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'Australia/Melbourne'), '%T'), @DOW=DAYOFWEEK(CONVERT_TZ(NOW(), 'UTC', 'Australia/Melbourne')), @FIRST=(SELECT programstarttime FROM wp_programgrid_programs WHERE dayofweek=DAYNAME(CONVERT_TZ(NOW(), 'UTC', 'Australia/Melbourne')) AND IsFirstProgramForDay=1), @LAST=(SELECT programstarttime FROM wp_programgrid_programs WHERE dayofweek=DAYNAME(CONVERT_TZ(NOW(), 'UTC', 'Australia/Melbourne')) ORDER BY programstarttime DESC LIMIT 1);

-- Query 2: Query the shit out of the data using our vars.
(select *, case dayofweek
        when 'Sunday' then 1
	when 'Monday' then 2
        when 'Tuesday' then 3
        when 'Wednesday' then 4
        when 'Thursday' then 5
        when 'Friday' then 6
        when 'Saturday' then 7
    end as dow
  from wp_programgrid_programs
        HAVING dow = IF((@TIME < @FIRST), IF((@DOW = 1), 7, @DOW-1), @DOW)
        AND programstarttime < IF((@TIME < @FIRST), '23:59:59', @TIME)
	order by programstarttime desc limit 1)
UNION
(select *, case dayofweek
        when 'Sunday' then 1
	when 'Monday' then 2
        when 'Tuesday' then 3
        when 'Wednesday' then 4
        when 'Thursday' then 5
        when 'Friday' then 6
        when 'Saturday' then 7
    end as dow
  from wp_programgrid_programs
	HAVING dow = @DOW
	and programstarttime > @TIME
        ORDER BY programstarttime limit 2)
UNION
  (select *, case dayofweek
        when 'Sunday' then 1
	when 'Monday' then 2
        when 'Tuesday' then 3
        when 'Wednesday' then 4
        when 'Thursday' then 5
        when 'Friday' then 6
        when 'Saturday' then 7
    end as dow
  from wp_programgrid_programs
	HAVING dow = IF((@DOW = 7), 1, @DOW+1)
        order by programstarttime LIMIT 2)
LIMIT 3;