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. 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.