总计一天的金额

Question

I am trying to sum the total amount of user payments for one day period (24 hours..)

My query is returning all results even if dates are far from 24 hours range.

How to fix this?

resultDaily = $this->getPayRepository()
        ->createQueryBuilder('p')
        ->select('sum(p.amount) Total')
        ->where('p.date >= :end')
        ->andWhere('p.user = :user')
        ->setParameter('end', new \DateTime('-1 day'))
        ->setParameter('user', $user)
        ->getQuery()
        ->getArrayResult();

I modified code. It look cleaner but gives the same result.

Edit:

$resultDaily = $this->getPayRepository()
        ->createQueryBuilder('p')
        ->select('sum(p.amount) Total')
        ->where("DATE_ADD(p.date, 24,'hour') >= CURRENT_TIMESTAMP()")
        ->andWhere('p.user = :user')
        ->setParameter('user', $user)
        ->getQuery()
        ->getArrayResult();

    return $resultDaily;

Answer 1

Thank you all for replay.

I did it with:

->where("DATE(p.transactionDate) >= DATE_SUB(CURRENT_DATE(), 24, 'hour')")