QueryBuilder : lt() on datetime does not exclude current object

[Peekmo]

Hello.

I have a problem with a filter in the query builder on a datetime field. I want the object with the date just before the given one, and the query returns an object with the same datetime (exactly the same)

    public function getPreviousNews(News $news)
    {
        $qb = $this->createQueryBuilder('news');
        $qb->where()->lt()->field('news.date')->literal($news->getDate())->end(); // Here
        $qb->orderBy()->desc()->field('news.date');
        $qb->setMaxResults(1);

        try {
            return $qb->getQuery()->getSingleResult();
        } catch (\Exception $ex) {
            return null;
        }
    }

Query generated

SELECT * FROM [nt:unstructured] AS news WHERE (news.date < CAST('1996-02-16T10:20:39.000+01:00' AS DATE) AND (news.[phpcr:class] = 'xxx\AppBundle\Document\News' OR news.[phpcr:classparents] = 'xxx\AppBundle\Document\News')) ORDER BY news.date DESC

And var_dump of the datetime from the object that I get

object(DateTime)#1573 (3) { ["date"]=> string(26) "1996-02-16 10:20:39.000000" ["timezone_type"]=> int(1) ["timezone"]=> string(6) "+01:00" } 

The problem does not appears with gt() method.

Thank you !

[dbu]

@dantleech would you have an idea what happens here?

@Peekmo a horrible workaround could be to substract 1 second from your date. unless there is some misunderstanding somewhere, this is a problem on the storage level. you could try to write a test for jackalope - checkout jackalope-doctrine-dbal, and add a test in vendor/phpcr/phpcr-api-tests/ in the folder with the query tests. if you manage to reproduce the problem on that level, it will help us a lot to pinpoint the problem (for example we will see if it happens with all databases or only sqlite or mysql or postgres)

[dantleech]

The query builder seems to be doing its job, the generated SQL2 looks like it should work, so I would say this is indeed a storage layer problem.

[Peekmo]

Thanks for your answer. I'll try tonight to add this test. It works only if I'm removing more than 1 hour to my date.

E.G

$qb->where()->lt()->field('news.date')->literal($news->getDate()->sub(new \DateInterval('PT1H'))->format('c'))->end(); // Not working

$qb->where()->lt()->field('news.date')->literal($news->getDate()->sub(new \DateInterval('PT59M'))->format('c'))->end(); // Not working

$qb->where()->lt()->field('news.date')->literal($news->getDate()->sub(new \DateInterval('PT1H01M'))->format('c'))->end(); // works

I'm using MySQL as database.

[dbu]

@Peekmo sorry for the long silence. can you try to provide a failing test so that we can investigate what happens, and see if its maybe a database layer issue?