Filter by date ignoring the time in DateTime object in Symfony 5-CodePudding

I am trying to filter a date using Symfony 5 query builder. But the DateTime object has time in it and the query builder uses the time in the query too.

In my controller, I am catching the POST variables submitted through the form and storing them in an array to pass into my repository.

$criteria['date'] = \DateTime::createFromFormat('d/m/Y', $request->get('date'));

And in my repository, I am building the query as follows.

$qb->andWhere('quotation.date = :date');
$qb->setParameter('date', $criteria['date']);

The problem is the database table column has the data type datetime, so when filtering nothing comes up because the DateTime object created has a wrong time in it.

Is there a way to filter only using the date part in PHP or in the database?

CodePudding user response：

The simplest way is to separate your DateTime object into Date and Time

CodePudding user response：

You could replace your strict comparison = by filtering between 00:00:00 and 23:59:59.

However, the answer I would recommend is using beberlei/DoctrineExtensions.

A set of extensions to Doctrine 2 that add support for functions available in MySQL, Oracle, PostgreSQL and SQLite.

One of those function is DATE which cast your datetime into a date to help with comparing datetime as date.

You can install it with composer

composer require beberlei/doctrineextensions

And you just have to configure which function you want to implement in your project in the doctrine.yaml file.

In your case you just need the DATE function :

doctrine:
    #...
    orm:
        #...
        entity_managers:
            default:
                #...
                dql:
                    datetime_functions:
                        DATE: DoctrineExtensions\Query\Postgresql\Date

And now you should be able to use the DATE function:

$qb->andWhere('DATE(quotation.date) = DATE(:date)');
$qb->setParameter('date', $criteria['date']);

CodePudding user response：

The easiest way: You put a "!" before the format.

$criteria['date'] = \DateTime::createFromFormat('!d/m/Y', $request->get('date'));

This sets the time to 00:00. Without the exclamation mark, the current time is always taken.