List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 26 2007 7:28pm
Subject:Re: return all dates between two dates
View as plain text  
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
> bcantwell@stripped
>  
> 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
>   

Attachment: [text/html]
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
Thread
return all dates between two datesBryan Cantwell26 Feb
  • Re: return all dates between two datesPeter Brawley26 Feb