Select by period

Question

I have this select.

SELECT 5125 matricula, CONCAT(fum.nomeguerra, ' / ' , fuc.nomeguerra) nomeguerra, date_format(es.data, '%d/%m/%Y') as data, es.semana, es.horarios, es.linha
FROM escala es
LEFT JOIN funcionario fuc ON fuc.matricula = es.matr_cobr
LEFT JOIN funcionario fum ON fum.matricula = es.matr_moto       
WHERE (matr_moto = 5125 or matr_cobr = 5125)
  AND es.data BETWEEN curdate() AND CURRENT_DATE()+6
ORDER BY es.data

At the bank I have every month and I need him to return only the current day plus 7 days forward. This works perfectly if this count is from the beginning of the month. If it is in the case on the 28th of the month it only takes until the end of the month, it does not pass to the next month.

line where he does this checking AND es.data BETWEEN curdate() AND CURRENT_DATE()+6

Answer 1

I believe that the most appropriate way to do this is by using the DATE_ADD():

SELECT 5125 matricula, CONCAT(fum.nomeguerra, ' / ' , fuc.nomeguerra) nomeguerra, date_format(es.data, '%d/%m/%Y') as data, es.semana, es.horarios, es.linha
FROM escala es
LEFT JOIN funcionario fuc ON fuc.matricula = es.matr_cobr
LEFT JOIN funcionario fum ON fum.matricula = es.matr_moto       
WHERE (matr_moto = 5125 or matr_cobr = 5125)
  AND es.data BETWEEN curdate() AND DATE_ADD(curdate(), INTERVAL 6 DAY)
ORDER BY es.data

---

It is worth noting that in this way your consultation uses functions in Where, something that this question explains well that this should be avoided for performance issues.

So, if you are using this query within any Procedure or only in the database, I advise creating a variable to store the value of the date(s)):

SELECT @data_atual := curdate(), @data_final := DATE_ADD(curdate(), INTERVAL 6 DAY);

SELECT 5125 matricula, CONCAT(fum.nomeguerra, ' / ' , fuc.nomeguerra) nomeguerra, date_format(es.data, '%d/%m/%Y') as data, es.semana, es.horarios, es.linha
FROM escala es
LEFT JOIN funcionario fuc ON fuc.matricula = es.matr_cobr
LEFT JOIN funcionario fum ON fum.matricula = es.matr_moto       
WHERE (matr_moto = 5125 or matr_cobr = 5125)
  AND es.data >= @data_atual 
  AND es.data <= @data_final
ORDER BY es.data

Obs: I also removed the between, thinking about the performance.