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 ;

Mariano L · Accepted Answer · 2017-03-09 17:01:34Z

1

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

answered Mar 9, 2017 at 17:01

Mariano L

1,8794 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL JOIN Syntax issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related