SELECT Multiple Date Range in Mysql

Question

Guys, I have a problem extracting dates on a range of trainings. What I need is the following if a training had its beginning on 01/01/2017 and its completion on 03/03/2017 raise should return in the select below for the months 01,02 and 03. Today this select only returns me the trainings that were created within a month.

SELECT  distinct * 
FROM treinamentos,usuario_x_treinamento,usuario
WHERE status_treinamento = 'REALIZADO' 
AND treinamentos.data_inicio_treinamento >='2017/01/1'
AND treinamentos.data_inicio_treinamento <='2017/01/31'
AND usuario_x_treinamento.id_usuario = usuario.id_usuario
AND usuario_x_treinamento.id_treinamento = treinamentos.id_treinamentos;

But since I’m only using the range at the start date I can’t make the selection I need.

PS : Select runs for all months of the year (it is used to generate a report).

What I need is to maintain the range Month by Month but also take into account the trainings that extend for more than a month.

Answer 1

If the DB is in date format, it is very simple (you can do with string too, but the performance is worse).

Suppose you want the trainings that were in effect in the month 5 of 2017:

SELECT DISTINCT
   *
FROM
   treinamentos,usuario_x_treinamento,usuario
WHERE
   status_treinamento = 'REALIZADO' 
   AND
      '201705' BETWEEN
      EXTRACT(YEAR_MONTH FROM treinamentos.data_inicio_treinamento) AND
      EXTRACT(YEAR_MONTH FROM treinamentos.data_fim_treinamento)
   AND
      usuario_x_treinamento.id_usuario = usuario.id_usuario
   AND
      usuario_x_treinamento.id_treinamento = treinamentos.id_treinamentos;

Points of interest:

• The function EXTRACT brings the year and month of a date in format YYYYMM when used with YEAR_MONTH. In this case, use zeros to the left of the month for comparison;

• As your report is monthly, we choose to separate this way to ignore the day and avoid complications of knowing last day of the month;

• The expression a BETWEEN c AND d amounts to ( a >= b AND a <= c ), simplifying the reading.