GroupBy and date

Question

I'm trying to get for each day, the amount of reservations. I have done this doctrine request :

   // prepare date for request
    $today = new DateTime('now');
    $lastMonth = new DateTime('-30 days');

    $qb
        ->select('r.reservationDate, sum(r.totalAmountNoTaxes) as totalAmountNoTaxes')
        ->where('r.companyId = :companyId')
        ->setParameter('companyId', $companyId)
        ->andWhere('r.reservationDate BETWEEN :month AND :today')
        ->setParameter('month', $lastMonth->format('Y-m-d'))
        ->setParameter('today', $today->format('Y-m-d'))
        ->orderBy('r.reservationDate', 'ASC')
        ->groupBy('r.reservationDate')
    ;

I get a result like that (from my webservice)

 {
      "reservationDate": "2016-12-21T07:41:12+0000",
      "totalAmountNoTaxes": "26150.30"
    },
    {
      "reservationDate": "2016-12-22T07:41:01+0000",
      "totalAmountNoTaxes": "230.00"
    },
    {
      "reservationDate": "2016-12-22T07:41:02+0000",
      "totalAmountNoTaxes": "21966.40"
    },
    {
      "reservationDate": "2016-12-23T07:40:52+0000",
      "totalAmountNoTaxes": "20407.05"
    },
    {
      "reservationDate": "2016-12-24T07:40:41+0000",
      "totalAmountNoTaxes": "4319.75"
    },
    {
      "reservationDate": "2016-12-24T07:40:42+0000",
      "totalAmountNoTaxes": "7261.20"
    },
    {
      "reservationDate": "2016-12-25T07:40:28+0000",
      "totalAmountNoTaxes": "14821.45"
    },
    {
      "reservationDate": "2016-12-26T07:40:16+0000",
      "totalAmountNoTaxes": "494.50"
    },
    {
      "reservationDate": "2016-12-26T07:40:17+0000",
      "totalAmountNoTaxes": "14541.25"
    },
    {
      "reservationDate": "2016-12-27T07:06:40+0000",
      "totalAmountNoTaxes": "25763.90"
    },
    {
      "reservationDate": "2016-12-28T05:37:43+0000",
      "totalAmountNoTaxes": "20595.70"
    }

The problem is that sometimes the dates are not grouped correctly. For exemple, the 2016-12-22 is split in 2. Same for 24, 26.

How can I do ?

Yes the dates are stored as timestamp and hence for 22nd there are 2 entries and its grouping as it is asked. You may only select as `date_format(r.reservationDate,'%Y-%m-%d') as reservationDate` and finally group by `reservationDate` — Abhik Chakraborty, Dec 30 '16 at 08:12
thx for answer, but I got [Syntax Error] line 0, col 7: Error: Expected known function, got 'date_format'" — Xero, Dec 30 '16 at 08:15
It seems like others have solved this, but for next time, if it was me, I'd get rid of all the doctrine stuff and just provide MySQL data viz. http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 30 '16 at 10:06

score 2 · Accepted Answer · answered Dec 30 '16 at 08:15

Looks like you have datetime field in your database. The solution would be group by date format:

//
->groupBy('DATE_FORMAT(r.reservationDate, '%Y%m%d')')
//

But DATE_FORMAT isnt implemented in default doctrine bundle, so you will have to add beberlei/DoctrineExtensions and then add the respective function to your doctrine bundle config like:

doctrine:
    dbal:
        ....
    orm:
        ....
        dql:
            string_functions:
                DATE_FORMAT: DoctrineExtensions\Query\Mysql\DateFormat

GroupBy and date

1 Answers1