# How the TIMEDIFF() function works in Mariadb?

The `TIMEDIFF()`

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

The `TIMEDIFF()`

function in MariaDB is used to calculate the difference between two time or date-time expressions. It returns the result as a time value in the format ‘HH:MM:SS’ or ‘HH:MM:SS.fraction’, depending on whether fractional seconds are included or not.

## Syntax

The syntax of the MariaDB `TIMEDIFF()`

function is as follows:

```
TIMEDIFF(expr1, expr2)
```

**expr1**: This is the first time or date-time expression.**expr2**: This is the second time or date-time expression.

Both `expr1`

and `expr2`

can be any valid time or date-time expression, such as a `TIME`

or `DATETIME`

value, or a function that returns a time or date-time value.

The function returns a time value representing the difference between `expr1`

and `expr2`

. If `expr1`

is greater than `expr2`

, the result is positive. If `expr1`

is less than `expr2`

, the result is negative.

## Examples

### Example 1: Calculating the difference between two time values

This example demonstrates how to calculate the difference between two time values using the `TIMEDIFF()`

function.

```
SELECT TIMEDIFF('10:30:00', '08:15:00') AS time_difference;
```

The following is the output:

```
+-----------------+
| time_difference |
+-----------------+
| 02:15:00 |
+-----------------+
```

In this example, the `TIMEDIFF()`

function calculates the difference between ‘10:30:00’ and ‘08:15:00’, resulting in ‘02:15:00’ (2 hours and 15 minutes).

### Example 2: Calculating the difference between a date-time and a time value

This example shows how to calculate the difference between a date-time value and a time value using the `TIMEDIFF()`

function.

```
SELECT TIMEDIFF('2023-05-01 10:30:00', '08:15:00') AS time_difference;
```

The following is the output:

```
+-----------------+
| time_difference |
+-----------------+
| NULL |
+-----------------+
```

In this example, the `TIMEDIFF()`

function can not calculate the difference between the date-time value ‘2023-05-01 10:30:00’ and the time value ‘08:15:00’, so it returns NULL.

### Example 3: Calculating the difference between two date-time values

This example demonstrates how to calculate the difference between two date-time values using the `TIMEDIFF()`

function.

```
SELECT TIMEDIFF('2023-05-01 10:30:00', '2023-05-01 08:15:00') AS time_difference;
```

The following is the output:

```
+-----------------+
| time_difference |
+-----------------+
| 02:15:00 |
+-----------------+
```

In this example, the `TIMEDIFF()`

function calculates the difference between the two date-time values ‘2023-05-01 10:30:00’ and ‘2023-05-01 08:15:00’, ignoring the date portions and returning the time difference as ‘02:15:00’.

### Example 4: Using `TIMEDIFF()`

with a table

This example shows how to use the `TIMEDIFF()`

function in combination with a table.

```
DROP TABLE IF EXISTS schedule;
CREATE TABLE schedule (
event_name VARCHAR(255),
start_time TIME,
end_time TIME
);
INSERT INTO schedule VALUES
('Meeting', '09:00:00', '10:30:00'),
('Presentation', '13:00:00', '15:15:00');
SELECT event_name, TIMEDIFF(end_time, start_time) AS duration
FROM schedule;
```

The following is the output:

```
+--------------+----------+
| event_name | duration |
+--------------+----------+
| Meeting | 01:30:00 |
| Presentation | 02:15:00 |
+--------------+----------+
```

In this example, the `TIMEDIFF()`

function is used to calculate the duration of each event by finding the difference between the `end_time`

and `start_time`

columns from the `schedule`

table.

### Example 5: Using `TIMEDIFF()`

with `CURRENT_TIME()`

function

This example demonstrates how to use the `TIMEDIFF()`

function in combination with the `CURRENT_TIME()`

function to calculate the time elapsed since a specific time.

```
SELECT TIMEDIFF(CURRENT_TIME(), '09:00:00') AS time_elapsed;
```

The following is the output:

```
+---------------+
| time_elapsed |
+---------------+
| 08:32:45 |
+---------------+
```

In this example, the `TIMEDIFF()`

function calculates the difference between the current time (obtained using `TIME()`

) and ‘09:00:00’. The result shows the time elapsed since 9:00 AM on the current day.

## Related Functions

The following are some functions related to the MariaDB `TIMEDIFF()`

function:

- MariaDB
`DATEDIFF()`

function is used to calculate the difference between two date values. - MariaDB
`TIMESTAMPDIFF()`

function is used to calculate the difference between two date-time values as an integer. - MariaDB
`TIME_TO_SEC()`

function is used to convert a time value into the corresponding number of seconds.

## Conclusion

The `TIMEDIFF()`

function in MariaDB is a versatile tool for calculating the difference between two time or date-time expressions. It can be used in various scenarios, such as calculating durations, time intervals, and more. By understanding the usage and capabilities of this function, along with related functions, developers can effectively work with time-related data in their MariaDB applications.