Mysql – Increase date by 1

MySQL

I have a query that fetch whole year record and sums up the count.The query is as follows 

select count(*) as t, m.machine1
from sap_prod_data s
left join sap_machine_speed m on s.mch_desc = m.machine 
where year(s.proddate) = '2016'
and m.machine1 in ('KBA','NewsLine','O-36','M-36','F-36','O-30','F-30')
and s.total_down_time>30
and s.plant IN('bla','bla')
and (
    select print_start_tm
    from sap_prod_data
    where year(proddate) = '2016' and edition = s.edition
    order by print_start_tm desc limit 1
)

i get the perfect result for this. If i add datetime range at the with 1 day add interval it shows 0 records. Like this 

select count(*) as t, m.machine1
from sap_prod_data s
left join sap_machine_speed m on s.mch_desc = m.machine
where year(s.proddate) = '2016'
and m.machine1 in ('bla','bla')
and s.total_down_time>30
and s.plantIN('bla')
and (
    select print_start_tm
    from sap_prod_data
    where proddate = '".$yesterday."' and edition = s.edition
    order by print_start_tm desc limit 1
) between 's.proddate 22:30:00'
          and 'DATE_ADD(s.proddate,INTERVAL 1 DAY) 02:30:00'

So is this above solution possible in Mysql.?

Best Answer

There is an error in this line:

 between 's.proddate 22:30:00'
          and 'DATE_ADD(s.proddate,INTERVAL 1 DAY) 02:30:00'

It should be:

select count(*) as t, m.machine1
from sap_prod_data s
left join sap_machine_speed m on s.mch_desc = m.machine
where year(s.proddate) = '2016'
and m.machine1 in ('bla','bla')
and s.total_down_time>30
and s.plantIN('bla')
and (
    select print_start_tm
    from sap_prod_data
    where proddate = '".$yesterday."' and edition = s.edition
    order by print_start_tm desc limit 1
) between CONCAT(DATE_FORMAT(s.proddate,"%Y-%m-%d"),' 22:30:00') 
          and DATE_ADD(s.proddate,INTERVAL '1:02:30:00' DAY_SECOND)
Related Question