MariaDB DATEDIFF Function

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