Home / Speed Up Queries / Question No: 20

I need to create an inline view that will give me all dates between two specified. Here is the question that I need to answer:

This basic query gets me the last 6 hours of history table entries for an item, grouped into minutes (if an item has a delay of 5 seconds, it takes the max out of each 11 entries per minutes).

SET @iid=231;
SET @hid=1;

SELECT from_unixtime(t.clock), 
       DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') as mins, 
       MAX(t.value) as value_max from history t 
WHERE t.itemid = (
                  SELECT itemid from items 
                   WHERE itemid = @iid and hostid = @hid
                 ) 
AND DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR) <= 
    from_unixtime(t.clock) GROUP BY mins ORDER BY t.clock;

It’ll fetch 360 entries (360 minutes in 6 hours) that looks like this:

from_unixtime(t.clock) mins value_max

2/24/2007 05:56:12 055 05:56 AM 0.29
2/24/2007 05:57:02 055 05:57 AM 0.2
2/24/2007 05:58:02 055 05:58 AM 0.23
2/24/2007 06:01:03 055 06:01 AM 0.02
2/24/2007 06:02:02 055 06:02 AM 0.08

The format you see for mins is DAY_OF_YEAR HOUR:MIN AM/PM. DAY_OF_YEAR is unique, as in 55th day of this year.

So if I can JOIN this table with another table/view/sub query that has EVERY MINUTE of the last 6 hours in a similarly formatted column like ‘mins’, we should be able to spot the missing 2 minutes.

from_unixtime(t.clock) mins value_max
2/24/2007 05:56:12 055 05:56 AM 0.29
2/24/2007 05:57:02 055 05:57 AM 0.2
2/24/2007 05:58:02 055 05:58 AM 0.23
2/24/2007 05:59:02 055 05:59 AM NULL <<- from the join
2/24/2007 06:00:02 055 06:00 AM NULL <<- from the join
2/24/2007 06:01:03 055 06:01 AM 0.02
2/24/2007 06:02:02 055 06:02 AM 0.08

A subqueryless (ie join) version of your query will likely be faster:

SELECT from_unixtime(t.clock),
        DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i %p') AS mins, 
        MAX(t.value) AS value_max FROM history t 
JOIN items i USING (itemid) WHERE t.itemid = @iid  
AND i.hostid = @hid AND 
DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) <= from_unixtime(t.clock)
GROUP BY mins ORDER BY t.clock;

For your second question, it might be easier to build a calendar table with one row for each possible & relevant datetime value, and left join that calendar table to your history table.