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

Dula 0
Viewed 34

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?

datetime symfony php
3 Answers

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

Randy Arnold0

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']);
Dylan KAS0

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.

jspit0
Related