From: Peter Brawley Date: February 26 2007 7:28pm Subject: Re: return all dates between two dates List-Archive: http://lists.mysql.com/mysql/205240 Message-Id: <45E334F1.30801@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-45E334F100BB=======" --=======AVGMAIL-45E334F100BB======= Content-Type: multipart/alternative; boundary=------------090301050302070803010909 --------------090301050302070803010909 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 > --------------090301050302070803010909 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 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
 
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
--------------090301050302070803010909-- --=======AVGMAIL-45E334F100BB======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-45E334F100BB=======--