Filter by date in Mysql

Asked

Viewed 3,375 times

1

I have a table where the registration date is stored in the DATE() format. The registration comes from 2013 and takes every day and month 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 lists 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;

Only he returns this way:

inserir a descrição da imagem aqui

And when I try to group the year:

inserir a descrição da imagem aqui

I only need the query, because I will use PHP for the listing.

mysql

2 answers

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;

  • Hello Gabriel. Okay, but how would I apply for my solution? Because I need to create this filter to do the way I mentioned in the post.

  • SELECT MONTH(Datacadastre) AS Mescadastro, YEAR(Datacadastro) AS Anocadastro FROM tabela Where Datacadastro between '01.01.2013' and '31.01.2013' GROUP BY Mescadastro;

1

Unfortunately I believe that there is no way to bring the results as you wish, but you can use the group_concat() to group the months with a separator and blow up 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 make a loop with php to group them:

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

https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-Concat http://php.net/manual/en/function.explode.php

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.