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');
MariaDB DATEDIFF
MariaDB DATEDIFF
SELECT DATEDIFF('2011-07-16 11:34:44', '2013-03-24');
MariaDB DATEDIFF Example
MariaDB DATEDIFF
SELECT DATEDIFF('2004-06-29', '2008-11-21');
Example of MariaDB DATEDIFF
MariaDB DATEDIFF
SELECT DATEDIFF('2008-11-21', '2004-06-29');
MariaDB DATEDIFF Example
MariaDB DATEDIFF

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');
MariaDB DATEDIFF CURDATE
MariaDB DATEDIFF CURDATE()

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_expr2It 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');
MariaDB TIMESTAMPDIFF
MariaDB TIMESTAMPDIFF

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');
MariaDB TIMESTAMPDIFF Negative Value
MariaDB TIMESTAMPDIFF Negative Value

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;
MariaDB TIMESTAMPDIFF in Hours
MariaDB TIMESTAMPDIFF in Hours

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;
MariaDB TIMESTAMPDIFF in Hours with Mixed Expressions
MariaDB TIMESTAMPDIFF in Hours with Mixed Expressions

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;
MariaDB TIMESTAMPDIFF in MICROSECOND
MariaDB TIMESTAMPDIFF in Microseconds

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;
MariaDB TIMESTAMPDIFF in MICROSECONDS
MariaDB TIMESTAMPDIFF in Microseconds

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;
MariaDB TIMESTAMPDIFF In Days From Current date
MariaDB TIMESTAMPDIFF In Days From Current date

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;
MariaDB TIMESTAMPDIFF NULL
MariaDB TIMESTAMPDIFF NULL

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;
MariaDB TIMESTAMPDIFF in Minutes
MariaDB TIMESTAMPDIFF in Minutes

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;
MariaDB TIMESTAMPDIFF in Months
MariaDB TIMESTAMPDIFF in Months

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;
MariaDB TIMESTAMPDIFF in Years
MariaDB TIMESTAMPDIFF in Years

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;
TIMESTAMPDIFF in Hours in MariaDB
TIMESTAMPDIFF in Hours in MariaDB

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;
MariaDB TIMESTAMPDIFF in Seconds
MariaDB TIMESTAMPDIFF in Seconds

Here, we are calculating the seconds between two different DateTime expressions.

You may also like to read the following MariaDB tutorials.

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