USE james;


CALL make_intervals(('2011-02-23 10:00:00' - INTERVAL 5 HOUR),('2011-02-23 10:00:00' + INTERVAL 6 HOUR),1,'HOUR');

SELECT * FROM time_intervals;

SELECT      ti.interval_start, l.*, tf.*, tt.*
FROM        time_intervals ti
LEFT OUTER JOIN   LAMP l ON (l.UTC = ti.interval_start)
LEFT OUTER JOIN   tafforecast tf ON (tf.forecastStart <= ti.interval_start AND tf.forecastEnd >= ti.interval_start AND tf.airportCode = l.airportCode)
LEFT OUTER JOIN   taftrend tt ON (tt.trendStart <= ti.interval_start AND tt.trendEnd >= ti.interval_start AND tf.airportCode = l.airportCode)
WHERE   l.airportCode = "LLT"
    OR l.UTC IS NULL OR tf.forecastStart IS NULL OR tt.trendStart IS NULL
GROUP BY ti.interval_start
ORDER BY ti.interval_start ASC;