SELECT Multiple Date Range in MySQL

Question

SELECT Multiple Date Range in MySQL

Navigation

#1 by (3 votes)

2

Staff have a problem with extracting dates in a training range. What I need is the following if a training started on 01/01/2017 and its completion on 03/03/2017 elevate 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 on the start date, I can not make the selection I need.

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

What I need is to keep the Month-to-Month range, but also take into account the training that goes on for more than a month.

mysql sql select

asked by anonymous 08.01.2017 / 23:07

1 answer

Questions about CSS I can not center the text

score 3 · Accepted Answer

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

Assuming you want the trainings that were in effect for 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 EXTRACT function returns the year and month of a date in the 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 have chosen to separate this way to skip the day and avoid complications of knowing the last day of the month;
The expression a BETWEEN c AND d is equal to ( a >= b AND a <= c ) , simplifying the reading.