How to use 'interval' in Doctrine2 Query Builder

Question

In my Symfony2 repository, I'd like to get objects from a schedule table, that have started, but not finished yet. The interval, within the objects should be encountered as 'not finished', should be passed as a variable.

Using plain SQL, it works like this:

SELECT * FROM slots rbs 
  WHERE rbs.rundate = '2012-08-13' 
  AND rbs.runtime <= '11:05:00' 
  AND '11:05:00' <= rbs.runtime + interval '300 seconds'
  ORDER BY rbs.rundate DESC, rbs.runtime DESC

Can I achieve the same with DQL / Query Builder?

This is what I have so far:

$qb = $this->createQueryBuilder('rbs');
$qb->where(
    $qb->expr()->andX(
         $qb->expr()->eq('rbs.rundate', ':date'),
         $qb->expr()->lte('rbs.runtime', ':time'),
         'rbs.runtime + interval 300 seconds >= :time'
        )
    )
  ->orderBy('rbs.rundate', 'DESC')
  ->addOrderBy('rbs.runtime', 'DESC')
  ->setParameter('date', date('Y-m-d'))
  ->setParameter('time', date('H:i:s'))

But this returns the following error:

[Doctrine\ORM\Query\QueryException]                                                 
[Syntax Error] line 0, col 139: Error: Expected =, <, <=, <>, >, >=, !=, got '300'

I found that 'interval' is not supported by Doctrine2 / DQL, which is also mentioned here.

Any suggestions on how to accomplish this with Doctrine2's Query Builder or DQL (and passing the interval as variable)?

Solution 1

As far as I know, Interval is not ported in Doctrine. The workaround I found is to work directly on the DateTime I pass as a param (here, I'd like to use interval of 2 days, passed through Datetime):

public function findOngoingPublicEvents()
{
    return $this->createQueryBuilder('e')
        ->where('e.isActive = 1')
        ->andWhere('e.isPublic = 1')
        ->andWhere('e.begin <= :begin')
        ->andWhere('e.end >= :end')
        ->orderBy('e.id', 'ASC')
        ->setParameter('begin', new \DateTime('+2 days'))
        ->setParameter('end', new \DateTime('-2 days'))
        ->getQuery()
        ->execute();
}

Solution 2

If you want to use INTERVAL (in Doctrine 2, DQL) on mysql comumn field, You can use as below,

$qb->andWhere("DATE_ADD(pv.myDAte,48,'hour') >= UTC_TIMESTAMP()");

It will print SQL as below,

...... DATE_ADD(p0_.appointment_date, INTERVAL 48 HOUR) >= UTC_TIMESTAMP() .....

Solution 3

@Kiran write only about DATE_ADD, but you can also use DATE_SUB

$qb->andWhere("DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE(), 6, 'day')");

It is equivalent of SQL:

DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)

Comments

[mediafreakch]

Thanks, the hint with \DateTime worked for me. Although as I only have a "runtime" field, I had to use it like this: ->where('rbs.runtime <= :now') ->andWhere('rbs.runtime >= :xbefore') ->setParameter('now', new \DateTime()) ->setParameter('xbefore', new \DateTime('-300 seconds') You can even replace the hardcoded value for \DateTime with a variable: new \DateTime('-'.$tolerance.' seconds')

[Ramon Dekkers]

It works. What about using value from column as time offset? Something like ->setParameter('begin', new \DateTime('+e.timeOffset days')). How can I achieve this?

[davmor]

Many of the same functions that you use INTERVAL for in PgSQL you can rewrite with Doctrine DQL functions: DATE_ADD(), DATE_SUB(), DATE_DIFF() -- check out CURRENT_DATE(), CURRENT_TIME(), and CURRENT_TIMESTAMP() as well. That's how I solved the issue. If you dig into the Doctrine code, DQL uses psql intervals to do this.

[nboulfroy]

I have use DATE_ADD(a.lastConnectionDate, 1, 'month') <= NOW() in my queryBuilder