Doctrine, Symfony | Difference between two dates in MySQL (Day, month, year…)

How do I know the difference between two dates in MySQL and Doctrine?

You have to use the TimeStampDiff method which takes 3 parameters (the unit, the first date, the second date) and returns a number corresponding to the difference in unit between the dates.

For example: 

//Year
TIMESTAMPDIFF(YEAR, 2019-01-01, 2022-01-01)
Sortie : 3 for the 3 ans of differences

//Month
TIMESTAMPDIFF(MONTH, 2019-01-01, 2019-06-01)
Sortie : 5 for the 5 mois of differences

//day
TIMESTAMPDIFF(DAY, 2019-01-01, 2019-01-16)
Sortie : 15 for the 15 jours of differences

//Minutes
TIMESTAMPDIFF(MINUTE, 2019-01-01 12:45:00, 2019-01-16 13:45:00)
Sortie : 60 for the 60 minutes of differences

See more details on this link which explains in detail the SQL function TIMESTAMPDIFF

How to use the SQL TimeStampDiff function under Doctrine with Symfony?

For this you need to install the Beberlei Doctrine Extensions package which is a complete and easy to use package for using SQL functions in Doctrine.

Once the package is installed, you have to initialize each function you want to use in the Doctrine config file like this : 

doctrine:
     dbal: ...
     orm: ...
     dql: 
         datetime_functions:
             TimestampDiff: DoctrineExtensions\Query\Mysql\TimestampDiff

It is possible to find all the functions of the Beberlei package in the vendor folder in his name.

Then to use the function in a DQL query in a Symfony Repository :

Example of a query to retrieve users based on the date of connection : 

public function getUsersDateConnexion(){
        $query = $this->createQueryBuilder('users')
                      ->join('u.historiqueConnexions', "connexion")
                      ->having("TIMESTAMPDIFF(MONTH, MAX(connexion.dateConnexion), CURRENT_DATE()) >= 36");
        return $query->getQuery()->getResult();
}

This function allows in this example to retrieve users not connected for 36 months, we see in the ->having that we can use the SQL TimeStampDiff function.

Thank you for reading don’t hesitate to share!