SQL JOIN Syntax issue

Question

So I have a table, PowerUse, in MySQL. It has two important columns, ID (long int) and timeRecorded (timeStamp). There are often long time gaps between entries. My objective is to query the table to get a result tells me how many records there are for each minute over 20 minute - including a 0 response for minutes without records.

Thanks to MySQL: Select All Dates In a Range Even If No Records Present I've got to the query below, but I can't get the dratted thing to work. Could someone point to the obvious error that I'm making, so I can stop banging my head against a brick wall? I know it's in the JOIN, I just can't see it. Thanks!

select count(PowerUse.id) + 0 as counter, timestamp((PowerUse.timeRecorded  div 100)*100) as time
from  PowerUse right join    
(
select date_add(NOW(), INTERVAL -n2.num*10-n1.num MINUTE) as dateb from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
 ) n2
 as ) datea 

 on (timestamp(PowerUse.timeRecorded  div 100)*100)  = dateb.datea 
   group by timeRecorded div 100 ;

score 1 · Accepted Answer · answered Mar 09 '17 at 17:01

I guess your best option is to create another table with:

start_date
end_date

and fill it with your ranges....

then you can join:

select start_date, count(PowerUse.id) + 0 as counter, timestamp((PowerUse.timeRecorded  div 100)*100) as time
from  PowerUse 
right join timeframes on (  timestamp((PowerUse.timeRecorded  div 100)*100) >= start_date and timestamp((PowerUse.timeRecorded  div 100)*100) < end_date )
group by start_date

Something like that

SQL JOIN Syntax issue

1 Answers1