Filter by date in Mysql

Question

Filter by date in Mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a table where the date of registration in DATE () format is stored. The registration comes from 2013 and picks up every day and months until December 2017:

2013-01-01
2013-01-02
2013-01-03
......

2017-12-01
2017-12-02
2017-12-03

I would like to filter as follows:

January 2013

..... Here you can see all records from January 1st to January 31st, 2013 ....

and so on until December 2017.

In mysql I tried this way:

SELECT MONTH(DataCadastro) AS MesCadastro, YEAR(DataCadastro) AS AnoCadastro FROM 'tabela' GROUP BY MesCadastro;

But it returns like this:

AndwhenItrytogrouptheyear:

I need only the query, because I'm going to use PHP for the listing.

mysql

asked by anonymous 08.01.2018 / 13:24

2 answers

Tabbed browsing problem Consumption of API with multiple requisitions

score 0 · Answer 1

use a where and the between command to filter between a range of values

SELECT MONTH(DataCadastro) AS MesCadastro, YEAR(DataCadastro) AS AnoCadastro 

FROM tabela where DataCadastro between '01.01.2013' and '31.01.2013' 

GROUP BY MesCadastro;

score 0 · Answer 2

Unfortunately I do not think there is a way to get the results you want, but you can use group_concat() to group the months with a separator and explode the string in php, which you said you will use for listing:

SELECT group_concat(MONTH(DataCadastro) separator ',') as MesesCadastro , YEAR(DataCadastro) AS AnoCadastro  FROM tabela where DataCadastro between '01.01.2013' and '31.01.2013' GROUP BY MesCadastro;

In php would be:

$lista[$result['AnoCadastro']] = explode(',', $result['MesesCadastro'] );
//resultado esperado -> $lista['2018'] => [1,2,3...]

Another option is to bring the data normally without grouping and looping with php to group them together:

$anos = [];
$foreach($result as $res){
   if(!isset($anos[ $res['AnoCadastro'] ])){
       $anos[ $res['AnoCadastro'] ] = [$res['MesCadastro']];
   }else{
       $anos[ $res['AnoCadastro'] ][]= $res['MesCadastro'];
   }
}

link link