In MariaDB, TIMESTAMPDIFF() is a built-in date and time function that returns the difference between two date or datetime expressions.

Syntax

The syntax goes like this:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Where unit is one of the following values:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

The units can optionally have a prefix of SQL_TSI_.

TIMESTAMPDIFF() returns datetime_expr2 – datetime_expr1.

One expression may be a date and the other a datetime. Date values are treated as having a time part of 00:00:00 where necessary.

Example

Here’s an example to demonstrate:

SELECT TIMESTAMPDIFF(DAY, '2030-02-01', '2030-03-01');

Result:

+------------------------------------------------+
| TIMESTAMPDIFF(DAY, '2030-02-01', '2030-03-01') |
+------------------------------------------------+
|                                             28 |
+------------------------------------------------+

Negative Result

Switching the dates around provides a negative result:

SELECT TIMESTAMPDIFF(DAY, '2030-03-01', '2030-02-01');

Result:

+------------------------------------------------+
| TIMESTAMPDIFF(DAY, '2030-03-01', '2030-02-01') |
+------------------------------------------------+
|                                            -28 |
+------------------------------------------------+

Datetime Values

Here’s an example of passing a datetime value:

SELECT TIMESTAMPDIFF(
    HOUR, 
    '2030-02-01 00:00:00', 
    '2030-02-01 12:30:45'
    ) 
AS Result;

Result:

+--------+
| Result |
+--------+
|     12 |
+--------+

I specified HOUR, and so it ignores the minutes and seconds portion.

Mixed Types

Here’s an example of passing both a date and a datetime value:

SELECT TIMESTAMPDIFF(
    HOUR, 
    '2030-02-01', 
    '2030-02-01 12:30:45'
    ) 
AS Result;

Result:

+--------+
| Result |
+--------+
|     12 |
+--------+

As mentioned, date values are treated as having a time part of 00:00:00.

Adding a SQL_TSI_ Prefix

The unit can include a SQL_TSI_ prefix if required:

SELECT TIMESTAMPDIFF(
    SQL_TSI_YEAR, 
    '2030-02-01', 
    '2035-02-01'
    )
AS Result;

Result:

+--------+
| Result |
+--------+
|      5 |
+--------+

Microseconds

Here’s an example that returns microseconds:

SELECT TIMESTAMPDIFF(
    MICROSECOND, 
    '2030-02-01 10:30:45.000000',
    '2030-02-01 10:30:45.123456'
    )
AS Result;

Result:

+--------+
| Result |
+--------+
| 123456 |
+--------+

Here’s one where microseconds aren’t actually specified in the datetime values:

SELECT TIMESTAMPDIFF(
    MICROSECOND, 
    '2030-02-01 10:30:45',
    '2030-02-01 12:30:45'
    )
AS Result;

Result:

+------------+
| Result     |
+------------+
| 7200000000 |
+------------+

Current Date

We can pass NOW() one of the the datetime arguments in order to compare the current date and time with another date:

SELECT 
    NOW(),
    TIMESTAMPDIFF(DAY, NOW(), '2021-03-31') AS Diff;

Result:

+---------------------+------+
| NOW()               | Diff |
+---------------------+------+
| 2021-05-30 09:29:01 |  -60 |
+---------------------+------+

Null Dates

If one of the dates is null, the result is null:

SELECT TIMESTAMPDIFF(
    YEAR, 
    '2030-02-01',
    NULL
    )
AS Result;

Result:

+--------+
| Result |
+--------+
|   NULL |
+--------+

Missing Argument

Calling TIMESTAMPDIFF() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT TIMESTAMPDIFF();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT TIMESTAMPDIFF('2020-12-09');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10, '2020-12-09')' at line 1