In this MariaDB tutorial, we will learn about MariaDB DATEDIFF with various examples. Additionally, we will cover the following topics.
- MariaDB DATEDIFF
- MariaDB DATEDIFF TIMESTAMPDIFF
- MariaDB DATEDIFF In Minutes
- MariaDB DATEDIFF Month
- MariaDB DATEDIFF Year
- MariaDB DATEDIFF Hour
- MariaDB DATEDIFF Seconds
MariaDB DATEDIFF
Here, we will learn about MariaDB DATEDIFF Function with its syntax and example.
The MariaDB DATEDIFF Function simply returns the difference in days between two different dates. It simply takes two dates as parameters and calculates the difference between them.
The syntax for the DATEDIFF Function in MariaDB is given below.
DATEDIFF( date1, date2 )
Here, date1 and date2 is the parameter whose values are calculated using the DATEDIFF function in MariaDB.
The calculation is done like this: date1 – date2
Several examples for the DATEDIFF function in MariaDB.
SELECT DATEDIFF('2019-09-26', '2018-05-17');

SELECT DATEDIFF('2011-07-16 11:34:44', '2013-03-24');

SELECT DATEDIFF('2004-06-29', '2008-11-21');

SELECT DATEDIFF('2008-11-21', '2004-06-29');

We can also calculate the days between a particular date and the current date using CURDATE() function which returns the current date.
To find the days between any particular date (be it ‘2004-06-29‘ ), type the following command.
SELECT DATEDIFF(CURDATE(), '2004-06-29');

In the above example, we are calculating the Date difference between the current date value and 2004-06-29. Note that the date format is not of the United States of America. It is in ‘YYY-MM-DD‘ format.
This is how the MariaDB DATEDIFF function works.
Read: How to Create View in MariaDB
MariaDB DATEDIFF TIMESTAMPDIFF
MariaDB TIMESTAMPDIFF Function is a built-in function that returns the difference between two dates or DateTime expression.
The syntax for the TIMESTAMPDIFF Function in MariaDB is given below.
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
Here,
unit – It is the unit in which the difference between two dates is needed to be calculated. The units in which we can calculate the difference are given below.
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
datetime_expr1, datetime_expr2 – It is the parameter in which we enter the two different DateTime expressions.
Several examples for the TIMESTAMPDIFF function in MariaDB.
Example 1 – TIMESTAMPDIFF function MariaDB
SELECT TIMESTAMPDIFF(DAY, '2013-06-07', '2030-08-08');

Here, we are calculating the days between two different DateTime expressions
Example 2 – Negative value in TIMESTAMPDIFF in MariaDB
SELECT TIMESTAMPDIFF(DAY, '2030-08-08', '2013-06-07');

Here, we are calculating the days between two different DateTime expressions and having negative results.
Example 3 – MariaDB TIMESTAMPDIFF in Hours
SELECT TIMESTAMPDIFF(
HOUR,
'2013-02-04 00:00:00',
'2013-02-01 12:30:45'
)
AS Result;

Here, we are calculating the no. of hours between two different DateTime expressions.
Example 4 – MariaDB TIMESTAMPDIFF in Hours with mixed DateTime expressions.
SELECT TIMESTAMPDIFF(
HOUR,
'2013-02-04',
'2013-02-01 12:30:45'
)
AS Result;

Here, we are calculating the no. of hours between two different DateTime expressions.
Example 5 – MariaDB TIMESTAMPDIFF in Microseconds.
SELECT TIMESTAMPDIFF(
MICROSECOND,
'2013-02-04 10:30:45.000000',
'2013-02-01 10:30:45.123456'
)
AS Result;

Here, we are calculating the microseconds between two different DateTime expressions.
Example 6 – MariaDB TIMESTAMPDIFF in Microseconds without specifying in DateTime expression.
SELECT TIMESTAMPDIFF(
MICROSECOND,
'2013-02-01 10:30:45',
'2013-02-01 12:40:45'
)
AS Result;

Here, we are calculating the microseconds between two different DateTime expressions without specifying in DateTime expression.
Example 7 – MariaDB TIMESTAMPDIFF In Days From Current date.
SELECT
NOW(),
TIMESTAMPDIFF(DAY, '2018-03-31', NOW()) AS Diff;

Here, we are calculating the days from the current date to different DateTime expressions.
Example 8 – MariaDB TIMESTAMPDIFF NULL.
SELECT TIMESTAMPDIFF(
YEAR,
'2015-12-21',
NULL
)
AS Result;

Here, we are calculating the days from NULL value to different DateTime expressions.
Read: Replace Function in MariaDB
MariaDB DATEDIFF In Minutes
Here, we will learn to find the time difference in minutes between two DateTime expressions in MariaDB.
We can find the time difference between two DateTime expressions using TIMEDIFF too in MariaDB but it will give the exact difference hours, minutes, and seconds in the result.
But here we want to get the difference in minutes only. So for this, we will use TIMESTAMPDIFF in MariaDB to find out the difference in minutes.
To find the time difference in minutes using TIMESTAMPDIFF in MariaDB, type the following command.
SELECT TIMESTAMPDIFF(
MINUTE,
'2013-02-01',
'2013-02-01 12:30:45'
)
AS Result;

Here, we are calculating the minutes between two different DateTime expressions.
Read: MariaDB Update Statement with Examples
MariaDB DATEDIFF Month
Here, we will learn to find the time difference in months between two DateTime expressions in MariaDB.
We can find the time difference between two DateTime expressions using DATEDIFF too in MariaDB but it will give the exact difference in days in the result.
But here we want to get the difference in months only. So for this, we will use TIMESTAMPDIFF in MariaDB to find out the difference in months.
To find the time difference in months using TIMESTAMPDIFF in MariaDB, type the following command.
SELECT TIMESTAMPDIFF(
MONTH,
'2013-02-01 10:30:45',
'2018-09-07'
)
AS Result;

Here, we are calculating the months between two different DateTime expressions.
Read: MariaDB create procedure
MariaDB DATEDIFF Year
Here, we will learn to find the time difference in years between two DateTime expressions in MariaDB.
We can find the time difference between two DateTime expressions using DATEDIFF too in MariaDB but it will give the exact difference in days in the result.
But here we want to get the difference in years only. So for this, we will use TIMESTAMPDIFF in MariaDB to find out the difference in years.
To find the time difference in years using TIMESTAMPDIFF in MariaDB, type the following command.
SELECT TIMESTAMPDIFF(
YEAR,
'2013-02-01',
'2031-09-07 09:50:25'
)
AS Result;

Here, we are calculating the year between two different DateTime expressions.
Read: How to Change Column in MariaDB
MariaDB DATEDIFF Hour
Here, we will learn to find the time difference in hours between two DateTime expressions in MariaDB.
We can find the time difference between two DateTime expressions using DATEDIFF too in MariaDB but it will give the exact difference in hours, minutes, and seconds in the result.
But here we want to get the difference in hours only. So for this, we will use TIMESTAMPDIFF in MariaDB to find out the difference in hours.
To find the time difference in hours using TIMESTAMPDIFF in MariaDB, type the following command.
SELECT TIMESTAMPDIFF(
YEAR,
'2013-02-01',
'2013-02-02 09:50:25'
)
AS Result;

Here, we are calculating the hours between two different DateTime expressions.
Read: MariaDB Insert Into + Examples
MariaDB DATEDIFF Seconds
Here, we will learn to find the time difference in seconds between two DateTime expressions in MariaDB.
We can find the time difference between two DateTime expressions using DATEDIFF too in MariaDB but it will give the exact difference in hours, minutes, and seconds in the result.
But here we want to get the difference in seconds only. So for this, we will use TIMESTAMPDIFF in MariaDB to find out the difference in seconds.
To find the time difference in seconds using TIMESTAMPDIFF in MariaDB, type the following command.
SELECT TIMESTAMPDIFF(
SECOND,
'2017-08-01',
'2017-08-01 09:50:25'
)
AS Result;

Here, we are calculating the seconds between two different DateTime expressions.
You may also like to read the following MariaDB tutorials.
- MariaDB Delete Row + Examples
- MariaDB GROUP BY with Example
- MariaDB ISNULL + Examples
- MariaDB Cast with Examples
- MariaDB Case Statement
- MariaDB Substring [11 Examples]
- MariaDB LIMIT + Examples
In this tutorial, we have learned about MariaDB DATEDIFF. Additionally, we have covered the following topics.
- MariaDB DATEDIFF
- MariaDB DATEDIFF TIMESTAMPDIFF
- MariaDB DATEDIFF In Minutes
- MariaDB DATEDIFF Month
- MariaDB DATEDIFF Year
- MariaDB DATEDIFF Hour
- MariaDB DATEDIFF Seconds
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.