List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:February 26 2007 4:40pm
Subject:return all dates between two dates
View as plain text  
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 55th 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
 
 
www.FireScope.net
 

Attachment: [text/html]
Attachment: [text/html]
Thread
return all dates between two datesBryan Cantwell26 Feb
  • Re: return all dates between two datesPeter Brawley26 Feb