Bryan
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.
PB
Bryan Cantwell wrote:
I need to create an inline view that will give me all dates between
two specified. Here is the question posed to me 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 55^th day of this year.
Okay, 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
Bryan Cantwell
Director, Development
(M) 469.361.1272
[EMAIL PROTECTED]
FireScope.net
www.FireScope.net
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
