How the TIMESTAMPDIFF() function works in Mariadb?

The TIMESTAMPDIFF() function in MariaDB is used to calculate the difference between two date or datetime expressions. It returns the difference as an integer value, representing the number of intervals between the two expressions based on the specified unit.

Syntax

The syntax of the MariaDB TIMESTAMPDIFF() function is as follows:

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

• unit: This is the unit in which the difference should be calculated. It can be one of the following values:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
• datetime_expr1: This is the first date or datetime expression.
• datetime_expr2: This is the second date or datetime expression.

The function returns an integer value representing the difference between datetime_expr1 and datetime_expr2, in terms of the specified unit. If datetime_expr1 is later than datetime_expr2, the result is positive. If datetime_expr1 is earlier than datetime_expr2, the result is negative.

Examples

Example 1: Calculating the difference in days

This example demonstrates how to calculate the difference between two dates in days using the TIMESTAMPDIFF() function.

SELECT TIMESTAMPDIFF(DAY, '2023-05-01', '2023-05-15');

The following is the output:

+------------------------------------------------+
| TIMESTAMPDIFF(DAY, '2023-05-01', '2023-05-15') |
+------------------------------------------------+
|                                             14 |
+------------------------------------------------+

In this example, the TIMESTAMPDIFF() function calculates the difference between ‘2023-05-01’ and ‘2023-05-15’ in days, resulting in 14.

Example 2: Calculating the difference in hours

This example shows how to calculate the difference between two datetime expressions in hours using the TIMESTAMPDIFF() function.

SELECT TIMESTAMPDIFF(HOUR, '2023-05-01 08:00:00', '2023-05-02 10:30:00');

The following is the output:

+-------------------------------------------------------------------+
| TIMESTAMPDIFF(HOUR, '2023-05-01 08:00:00', '2023-05-02 10:30:00') |
+-------------------------------------------------------------------+
|                                                                26 |
+-------------------------------------------------------------------+

In this example, the TIMESTAMPDIFF() function calculates the difference between ‘2023-05-01 08:00:00’ and ‘2023-05-02 10:30:00’ in hours, resulting in 26.

Example 3: Calculating the difference in months

This example demonstrates how to calculate the difference between two dates in months using the TIMESTAMPDIFF() function.

SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-05-01');

The following is the output:

+--------------------------------------------------+
| TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-05-01') |
+--------------------------------------------------+
|                                               16 |
+--------------------------------------------------+

In this example, the TIMESTAMPDIFF() function calculates the difference between ‘2022-01-01’ and ‘2023-05-01’ in months, resulting in 16.

Example 4: Using TIMESTAMPDIFF() with a table

This example shows how to use the TIMESTAMPDIFF() function in combination with a table.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    delivery_date DATE
);
INSERT INTO orders VALUES
    (1, '2023-05-01', '2023-05-05'),
    (2, '2023-05-10', '2023-05-15');

SELECT order_id,
       TIMESTAMPDIFF(DAY, order_date, delivery_date) AS delivery_time
FROM orders;

The following is the output:

+----------+---------------+
| order_id | delivery_time |
+----------+---------------+
|        1 |             4 |
|        2 |             5 |
+----------+---------------+

In this example, the TIMESTAMPDIFF() function calculates the difference between the order_date and delivery_date columns from the orders table in days, and the result is included in the output along with the order_id.

Example 5: Using TIMESTAMPDIFF() with multiple units

This example demonstrates how to use the TIMESTAMPDIFF() function with multiple units to calculate complex intervals.

SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-05-01') AS years,
       TIMESTAMPDIFF(MONTH, '2020-01-01', '2023-05-01') AS months,
       TIMESTAMPDIFF(DAY, '2020-01-01', '2023-05-01') AS days;

The following is the output:

+-------+--------+------+
| years | months | days |
+-------+--------+------+
|     3 |     40 | 1216 |
+-------+--------+------+

In this example, the TIMESTAMPDIFF() function is used to calculate the difference between ‘2020-01-01’ and ‘2023-05-01’ in years, months, and days. The modulus operator (%) is used to get the remaining months and days after accounting for the years and months, respectively.

Related Functions

The following are some functions related to the MariaDB TIMESTAMPDIFF() function:

• MariaDB DATEDIFF() function is used to calculate the difference between two date values as the number of days.
• MariaDB TIMEDIFF() function is used to calculate the difference between two time or datetime expressions as a time value.
• MariaDB TIMESTAMPADD() function is used to add an interval to a date or datetime expression.

Conclusion

The TIMESTAMPDIFF() function in MariaDB is a powerful tool for calculating the difference between two date or datetime expressions in various units. It can be used in a wide range of scenarios, such as age calculations, duration calculations, and data analysis. By understanding the usage and capabilities of this function, along with related functions, developers can effectively work with date and time data in their MariaDB applications.