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)?

Midway

Answer 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();
}

Held

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') – Midway

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? – Franke

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. – Rileyrilievo

Answer 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() .....

Ekaterina

Answer 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)

Necessaries

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

Answer 4

AFAIK in Doctrine yet there's no support for postgres interval. However it is possible to write custom mapping of column as well as dbal type. Here's an example:

#[ORM\Column(type: 'carbon_interval', columnDefinition: 'INTERVAL NOT NULL')]
private CarbonInterval $duration;

Wrapper type for carbon:

#[Immutable]
final class CarbonIntervalPostgresType extends DateIntervalPostgresPostgresType
{
    public const NAME = 'carbon_interval';

    public function convertToPHPValue($value, AbstractPlatform $platform): ?CarbonInterval
    {
        //1 years 0 mons 0 days 0 hours 0 mins 0.0 secs
        $interval = parent::convertToPHPValue($value, $platform);

        if ($interval instanceof DateInterval) {
            $interval = CarbonInterval::instance($interval);
        }

        return $interval;
    }
}

Overwrite dateinterval doctrine type to use postgres format. Note that in existing codebase it may break a lot of stuff so be careful and do not overwrite if you are not sure.

#[Immutable]
class DateIntervalPostgresPostgresType extends IntervalPostgresType
{
    public const NAME = Types::DATEINTERVAL;
}

Next class is responsible for providing appropriate postgresql interval format and php DateInterval converation. BTW, if you define getSQLDeclaration, then you don't even need to write columnDefinition manually.

#[Immutable]
class IntervalPostgresType extends Type
{
    public const NAME = 'interval';

    /** @see \Doctrine\DBAL\Types\Type::getName() */
    public function getName(): string
    {
        return static::NAME;
    }

    /** @see \Doctrine\DBAL\Types\Type::getSQLDeclaration() */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform): string
    {
        return static::NAME;
    }

    /** @see \Doctrine\DBAL\Types\Type::convertToDatabaseValue() */
    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if ($value === null) {
            return null;
        }

        if (!$value instanceof DateInterval) {
            throw new InvalidArgumentException('Interval value must be instance of DateInterval');
        }

        $parts = array(
            'y' => 'year',
            'm' => 'month',
            'd' => 'day',
            'h' => 'hour',
            'i' => 'minute',
            's' => 'second',
        );

        $sql = '';
        foreach ($parts as $key => $part) {
            $val = $value->{$key};
            if (empty($val)) {
                continue;
            }
            $sql .= " {$val} {$part}";
        }

        return trim($sql);
    }

    /**
     * @throws ConversionException
     * @see \Doctrine\DBAL\Types\Type::convertToPHPValue()
     */
    public function convertToPHPValue($value, AbstractPlatform $platform): ?DateInterval
    {
        if ($value === null) {
            return null;
        }

        $matches = array();
        preg_match(
            '/(?:(?P<y>[0-9]+) (?:year|years))?'
            .' ?(?:(?P<m>[0-9]+) (?:months|month|mons|mon))?'
            .' ?(?:(?P<d>[0-9]+) (?:days|day))?'
            .' ?(?:(?P<h>[0-9]{2}):(?P<i>[0-9]{2}):(?P<s>[0-9]{2}))?/i',
            $value,
            $matches
        );

        if (empty($matches)) {
            throw ConversionException::conversionFailed($value, static::NAME);
        }

        $interval = new DateInterval('PT0S');

        if (!empty($matches['y'])) {
            $interval->y = (int)$matches['y'];
        }

        if (!empty($matches['m'])) {
            $interval->m = (int)$matches['m'];
        }

        if (!empty($matches['d'])) {
            $interval->d = (int)$matches['d'];
        }

        if (!empty($matches['h'])) {
            $interval->h = (int)$matches['h'];
        }

        if (!empty($matches['i'])) {
            $interval->i = (int)$matches['i'];
        }

        if (!empty($matches['s'])) {
            $interval->s = (int)$matches['s'];
        }

        return $interval;
    }
}

Lillalillard