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

Web developer Front-End in Oise, France - Gary Deshayes <--Traduction

Created at : Thursday 18 June 2020


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!

News publications :
Symfony

Created at : Monday 1 November 2021

Javascript Framework JS

Created at : Sunday 31 October 2021

Symfony

Created at : Wednesday 1 September 2021

Javascript Framework JS

Created at : Saturday 28 November 2020

Javascript SQL Framework JS

Created at : Sunday 20 September 2020

Symfony

Created at : Thursday 6 August 2020

Javascript jQuery

Created at : Monday 23 March 2020

Symfony

Created at : Friday 14 February 2020

Symfony Bugs

Created at : Sunday 26 January 2020