MySQL: Get number of Days between two Timestamps

For this purpose, MySQL is providing the function DATEDIFF(). You can pass 2 dates to this function and you get back the time difference between them.

For example like that:

SELECT DATEDIFF('2031-01-01', '2021-01-01') AS d;

Here, we are calculating the number of days between January 1, 2021 and January 1, 2031. To ensure that the result will be positive, the first date must be greater than the second one. Otherwise, we will get a negative value.

Of course, we can also directly fetch the date from a table:

SELECT id, DATEDIFF('2021-01-01', dat) FROM tab;

In this example, we are reading out the records from the table "tab". In this table, there is also the column "dat" in which the data is stored. Here we would like to get the time difference between this date and January, 1 2021.

Instead of using a fixed date, we can also use the current date using CURDATE():

SELECT DATEDIFF('2031-01-01', CURDATE()) AS d;

This query tells us how many days there are between now and January, 1 2031.

If we would like to specify the age of the records in our database, we can also make use of CURDATE() and combine both:

SELECT id, DATEDIFF(CURATE(), dat) FROM tab;

By the way, there is also the function TIMEDIFF() with which we can calculate the difference between two timestamps more exactly.
Last update on 2021-07-19 | Created on 2016-02-11