How the SUBTIME() function works in Mariadb?

The SUBTIME() function in MariaDB is used to subtract a time value from a datetime or time value.

Posted on

The SUBTIME() function in MariaDB is used to subtract a time value from a datetime or time value. It takes two expressions as arguments and returns the result in the same format as the first expression.

Syntax

The syntax for the MariaDB SUBTIME() function is as follows:

SUBTIME(expr1, expr2)
  • expr1: This is a datetime or time expression from which the time value will be subtracted.
  • expr2: This is a time expression that will be subtracted from expr1.

The function returns the result in the same format as expr1, which means that if expr1 is a datetime, the result will be a datetime value, and if expr1 is a time, the result will be a time value.

Examples

Example 1: Subtracting a time value from a datetime

This example demonstrates how to subtract a time value from a datetime value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (dt DATETIME);
INSERT INTO example VALUES ('2023-03-09 10:30:00'), ('2023-03-09 12:45:30');

SELECT dt, SUBTIME(dt, '00:30:00') AS result
FROM example;

The output of this statement is:

+---------------------+---------------------+
| dt                  | result              |
+---------------------+---------------------+
| 2023-03-09 10:30:00 | 2023-03-09 10:00:00 |
| 2023-03-09 12:45:30 | 2023-03-09 12:15:30 |
+---------------------+---------------------+

In this example, the SUBTIME() function subtracts 30 minutes from the datetime values in the dt column, resulting in a new datetime value.

Example 2: Subtracting a time value from a time

This example shows how to subtract a time value from a time value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (t TIME);
INSERT INTO example VALUES ('10:30:00'), ('12:45:30');

SELECT t, SUBTIME(t, '00:30:00') AS result
FROM example;

The output of this statement is:

+----------+----------+
| t        | result   |
+----------+----------+
| 10:30:00 | 10:00:00 |
| 12:45:30 | 12:15:30 |
+----------+----------+

In this example, the SUBTIME() function subtracts 30 minutes from the time values in the t column, resulting in a new time value.

Example 3: Subtracting a time value with negative sign

This example shows how to subtract a negative time value from a datetime value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (dt DATETIME);
INSERT INTO example VALUES ('2023-03-09 10:30:00');

SELECT dt, SUBTIME(dt, '-01:00:00') AS result
FROM example;

The output of this statement is:

+---------------------+---------------------+
| dt                  | result              |
+---------------------+---------------------+
| 2023-03-09 10:30:00 | 2023-03-09 11:30:00 |
+---------------------+---------------------+

In this example, the SUBTIME() function subtracts a negative time value (which is equivalent to adding a positive time value) from the datetime value, resulting in a new datetime value.

Example 4: Subtracting a time value with hours exceeding 24

This example demonstrates what happens when you subtract a time value with hours exceeding 24 from a datetime value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (dt DATETIME);
INSERT INTO example VALUES ('2023-03-09 10:30:00');

SELECT dt, SUBTIME(dt, '48:00:00') AS result
FROM example;

The output of this statement is:

+---------------------+---------------------+
| dt                  | result              |
+---------------------+---------------------+
| 2023-03-09 10:30:00 | 2023-03-07 10:30:00 |
+---------------------+---------------------+

In this case, MariaDB subtracts the time value (48 hours) from the datetime value, which results in a new datetime value that is two days earlier than the original value.

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

  • The MariaDB DATE_SUB() function is used to subtract a specified time value from a date or datetime value.
  • The MariaDB ADDTIME() function is used to add a time value to a datetime or time value.
  • The MariaDB TIMEDIFF() function is used to calculate the difference between two time or datetime values.
  • The MariaDB DATEDIFF() function is used to calculate the difference between two date or datetime values in terms of days.

Conclusion

The SUBTIME() function in MariaDB is a useful tool for manipulating and calculating time values. It allows you to subtract a time value from a datetime or time value, making it easy to perform various calculations involving time intervals. By understanding the function’s syntax and behavior, as well as related functions, you can effectively work with time-based data in your MariaDB databases.