# How the TIMESTAMPDIFF() function works in Mariadb?

The `TIMESTAMPDIFF()`

function in MariaDB is used to calculate the difference between two date or datetime expressions.

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.