Get data X days ago using pgsql and doctrine
I am working on Symfony2 and I have an entity Account
with a inscriptionDate
type field datetime
.
I need to get the number of labels X days ago and I have some problems, at the moment I have:
$qb = $this->_em->createQueryBuilder();
$qb->select('COUNT(a.id)')
->from('MyProjectBundle:Account', 'a')
->where('a.inscriptionDate = DATE_SUB(NOW(), INTERVAL ?1 DAY) ')
->setParameters(array(1 => $daysAgo));
return $qb->getQuery()->getSingleScalarResult();
I have a bug on CURDATE()
. How can i do this? I need to count all the labels on NOW() - X days
and I dont want to use hour-minute-second
in my datetime.
+3
source to share
1 answer
You can use DATE_DIFF instead of DATE_SUB to match a condition like "X days ago".
For example:
$qb = $this->_em->createQueryBuilder();
$qb->select('COUNT(a.id)')
->from('MyProjectBundle:Account', 'a')
->where('DATE_DIFF(CURRENT_DATE(), a.inscriptionDate) > ?1')
->setParameters(array(1 => $daysAgo));
return $qb->getQuery()->getSingleScalarResult();
+3
source to share