Brave publisher PUBLISHER
Web developer Front-End in Oise, France - Gary Deshayes

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?


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 5 | Return an image via a controller route

Symfony

Created at : Wednesday 1 September 2021

VueJS 3, ExpressJS 4.17 | Uploading and resizing an image

Javascript Framework JS

Created at : Saturday 28 November 2020

Symfony Event Subscriber | Restricting an ip range with an Event Subscriber

Symfony

Created at : Saturday 14 November 2020

Angular, ExpressJS | JWT authentication with Angular 10 and ExpressJS 4 (MySQL)

Javascript SQL Framework JS

Created at : Sunday 20 September 2020

Symfony, Excel, CSV | Generating a CSV file for Excel with Symfony

Symfony

Created at : Thursday 6 August 2020

Symfony FormType Choice | Set data default to group of radio button

Symfony

Created at : Sunday 26 July 2020

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

Symfony SQL

Created at : Thursday 18 June 2020

JavaScript, jQuery et Regex | Secure a password in real time with JS

Javascript jQuery

Created at : Monday 23 March 2020

Symfony, Doctrine | Retrieve old data of FormType

Symfony

Created at : Friday 14 February 2020

jQuery, Webpack and Symfony | Call jQuery in your twig files

Symfony Bugs

Created at : Sunday 26 January 2020