How to compare datetime field from Doctrine2 with a date?

Question

I want to get the items which were created today with by a QueryBuilder from Doctrine2. I want to compare the createdAt(Datetime) field with today parameter(Date). Is it possible to do that in one query?

$qb = $this->createQueryBuilder('i');
$qb->innerJoin('i.type', 'it');
$qb->andWhere('it.name = :type');
$qb->andWhere('i.createdAt < :today');
// i.createdAt == datetime and :today parameter is a date

stwe · Accepted Answer · 2012-05-21T17:15:21.977

one idea is to extract from the date: the year, month and day. And then

$qb->select('p')
   ->where('YEAR(p.postDate) = :year')
   ->andWhere('MONTH(p.postDate) = :month')
   ->andWhere('DAY(p.postDate) = :day');

$qb->setParameter('year', $year)
   ->setParameter('month', $month)
   ->setParameter('day', $day);

MONTH DAY, and YEAR you take out the DoctrineExtensions from

e.g.

DoctrineExtensions

This works for me. You only need the files: day.php, month.php and year.php.....

You get the month e.g.:

    $datetime = new \DateTime("now");
    $month = $datetime->format('m');
    echo $month;

Copy day.php, month.php and year.php to your bundle Xy\TestBundle\Dql Register the new functions in app\config.yml with

doctrine:


orm:
    auto_generate_proxy_classes: %kernel.debug%
    entity_managers:
        default:
            auto_mapping: true
            dql:
                datetime_functions:
                    month: Xy\TestBundle\Dql\Month
                    year: Xy\TestBundle\Dql\Year
                    day: Xy\TestBundle\Dql\Day

It working, but it's little bit dirty. Do you know if DATE() function for QueryBuilder is provided with this bundle? — gperriard, May 21 '12 at 14:50
@jooyce there isn't. [Here](https://github.com/simukti/DoctrineExtensions/tree/master/lib/DoctrineExtensions/Query/Mysql) is the list of extensions available — David Barreto, Sep 04 '12 at 00:25

score 17 · Answer 2 · answered Oct 21 '14 at 09:17

There is better option than adding the doctrine extension for date.

First you need to get start-datetime and end-datetime :

$today_startdatetime = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 00:00:00") );
$today_enddatetime = \DateTime::createFromFormat( "Y-m-d H:i:s", date("Y-m-d 23:59:59") );

Now use them in query :

$em = \Zend_Registry::get('em');
$qb_1 = $em->createQueryBuilder();
$q_1 = $qb_1->select('wsh')
    ->from('\Entities\wishes', 'wsh')
    ->where('wsh.created_at >= :today_startdatetime')
    ->andWhere('wsh.created_at <= :today_enddatetime');


$q_1->setParameter('today_startdatetime', $today_startdatetime);
$q_1->setParameter('today_enddatetime', $today_enddatetime);
$result= $q_1->getQuery ()->getResult ();

this is a more straight forward answer. SAD that ORM doesn't provide specific function for this — jrran90, Feb 08 '17 at 21:47

score 15 · Answer 3 · answered May 23 '15 at 18:55

15

It is also possible to use built-in function DATE_DIFF(date1, date2) which returns difference in days. Check docs

$result = $this->createQueryBuilder('l')
    ->where('DATE_DIFF(l.startDate, CURRENT_DATE()) = 0')

answered May 23 '15 at 18:55

Vitalii Zurian

17,858
4
64
81

score 13 · Answer 4 · edited Jul 09 '13 at 20:20

13

It's rare that such a mature ORM does not provide the DATE function. However, to get a date out of a datetime field, you can apply the SUBSTRING function like this:

SELECT SUBSTRING(i.dateTimeField,1,10) FROM tableName i

Hope it helps!

edited Jul 09 '13 at 20:20

sth

222,467
53
283
367

answered Jul 09 '13 at 19:56

Pablo Gonzalez Portela

189
1
9

Tks for you answered!Helps a lot! – Sergio Costa Feb 28 '14 at 01:53
Kind of hacky fix, until DATE function is supported by Doctrine! thanks,, – Muzafar Ali Oct 29 '17 at 09:02

score 5 · Answer 5 · answered May 21 '12 at 09:57

5

You have to add to your query QueryBuilder the today parameter.

$today = new \DateTime();
$qb->setParameter('today', $today->format('Y-m-d'));

With the QueryBuilder, you can compare dates to DateTime with the format 'Y-m-d'

answered May 21 '12 at 09:57

Reuven

3,336
2
23
29

I tried to manage with $today->format('Y-m-d') as parameter but I've got no result. I need to get only 'Y-m-d' for 'createdAt' field. – gperriard May 21 '12 at 12:03
Have you tried to directly pass a `DateTime` object as a parameter ? – Florian Klein May 21 '12 at 12:59
Yes, but that's not the same value (when I compare them I've got false) and It's not exactly what I would. I would to get items that were created on the same day, but at any time. i.createdAt = 2012-05-21 13:18:27 $today = 2012-05-21 14:23:34 – gperriard May 21 '12 at 13:41

score 3 · Answer 6 · answered Nov 12 '15 at 11:08

An easy solution would be to format the datetime accordingly

public function getWithStartingPremium(\DateTime $datetime)
{
    $qb = $this->createQueryBuilder('c');

    return $qb
        ->innerJoin('c.collegePremium', 'cp')
        ->where($qb->expr()->eq('cp.start', ':date'))
        ->setParameters(array(
            'date' => $datetime->format('Y-m-d'),
        ))
        ->getQuery()
        ->getResult();
}

score 0 · Answer 7 · answered Mar 25 '22 at 10:09

If you don't want to add an external extension just for this, you can search your datetime as a string.

$today = new \DateTime();

$qb = $this->createQueryBuilder('i');
$qb->innerJoin('i.type', 'it');
$qb->andWhere('it.name = :type');
$qb->andWhere('i.createdAt LIKE :today');
$qb->setParameter('today', $today->format('Y-m-d').'%');

PS: I know this is an old question, but I had this problem and used this solution.

How to compare datetime field from Doctrine2 with a date?

7 Answers7

Linked