MySQL query GROUP BY day/month/year

Question

Is it possible to make a simple query to count the number of records I have in a specified time period like a year, month or day, having a field, like: public function group(){

$em = $this->getEntityManager();
$sub = $em->createQueryBuilder();

$qb = $sub;
$qb->select('DAY(s.start_date), YEAR(s.start_date), MONTH(s.start_date), COUNT(*)')
    ->from('App\Entity\Solts', 's')
    ->groupBy("DAY(s.start_date), YEAR(s.start_date), MONTH(s.start_date)" );
         
$query = $sub -> getQuery();

return $query -> getResult();

}

I want him to show me like this the result : => enter image description here

Doing that is fundamentally the same as just grouping by `start_date`, ie, counting rows per day. — Alejandro, Oct 19 '22 at 21:24
`DAY()`, `YEAR()` and `MONTH()` are not callable with DQL without creating your own dql function (or using a package), is that your main issue ? — Dylan KAS, Oct 20 '22 at 09:16

score 1 · Answer 1 · answered Oct 20 '22 at 13:44

Based on this response https://stackoverflow.com/a/48047792/3866856 you can try:

return $qb =   $this->createQueryBuilder('s')
                ->select('DATE_FORMAT(s.start_date, \'%Y-%m-%d\') as yearMonthDay, DATE_FORMAT(e.start_date, \'%Y\') as d_year, DATE_FORMAT(e.start_date, \'%m\') as d_month, DATE_FORMAT(e.start_date, \'%d\') as d_day,  count(s.id) countItems')
                ->groupBy('yearMonthDay')
                ->getQuery()
                ->getResult();

MySQL query GROUP BY day/month/year

1 Answers1