From: Bryan Cantwell Date: February 26 2007 4:40pm Subject: return all dates between two dates List-Archive: http://lists.mysql.com/mysql/205236 Message-Id: MIME-Version: 1.0 Content-Type: multipart/related; type="multipart/alternative"; boundary="----_=_NextPart_001_01C759C4.D1EB3E84" ------_=_NextPart_001_01C759C4.D1EB3E84 Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01C759C4.D1EB3E84" ------_=_NextPart_002_01C759C4.D1EB3E84 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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: =20 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). =20 set @iid=3D231; set @hid=3D1; =20 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 =3D (select itemid from items where itemid =3D @iid and hostid = =3D @hid) and DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) <=3D from_unixtime(t.clock) group by mins order by t.clock; =20 =20 It'll fetch 360 entries (360 minutes in 6 hours) that looks like this: =20 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 =20 =20 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. =20 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. =20 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 =20 =20 Bryan Cantwell Director, Development (M) 469.361.1272 bcantwell@stripped =20 =20 www.FireScope.net =20 ------_=_NextPart_002_01C759C4.D1EB3E84 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
I need = to create an=20 inline view that will give me all dates between two specified. Here is = the=20 question posed to me that I need to answer:
 

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

 

set=20 @iid=3D231;

set=20 @hid=3D1;

 

select=20 from_unixtime(t.clock), DATE_FORMAT(from_unixtime(t.clock),'%j %h:%i = %p') as=20 mins, MAX(t.value) as value_max from history t where t.itemid = =3D=20 (select itemid from items where itemid =3D @iid and hostid =3D @hid) and = DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR) <=3D = from_unixtime(t.clock)=20 group by mins order by=20 t.clock;

 

 

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

 

from_unixtime(t.clock)=20 mins           &nb= sp;    =20 value_max

2/24/2007=20 05:56:12        055 05:56=20 AM    0.29

2/24/2007=20 05:57:02        055 05:57=20 AM    0.2

2/24/2007=20 05:58:02        055 05:58=20 AM    0.23

2/24/2007=20 06:01:03        055 06:01=20 AM    0.02

2/24/2007=20 06:02:02        055 06:02=20 AM    0.08

 

 

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

 

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

 

from_unixtime(t.clock)   =20 mins           &nb= sp;    =20 value_max

2/24/2007=20 05:56:12        055 05:56=20 AM    0.29

2/24/2007=20 05:57:02        055 05:57=20 AM    0.2

2/24/2007=20 05:58:02        055 05:58=20 AM    0.23

2/24/2007=20 05:59:02        055 05:59=20 AM    NULL  <ß from the=20 join

2/24/2007=20 06:00:02        055 06:00=20 AM    NULL  <<- from the=20 join

2/24/2007=20 06:01:03        055 06:01=20 AM    0.02

2/24/2007=20 06:02:02        055 06:02=20 AM    = 0.08

 
 
Bryan=20 Cantwell
Director, = Development
(M)=20 469.361.1272
bcantwell@stripped
 
3DFireScope.net
www.FireScope.net
 
------_=_NextPart_002_01C759C4.D1EB3E84-- ------_=_NextPart_001_01C759C4.D1EB3E84--