How to use the MySQL SUBTIME() function

In this article, we will learn how to use the MySQL SUBTIME() function, which subtracts a time value from a date or datetime value, or a time value from another time value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates, datetimes, and times.

Syntax

The syntax of the SUBTIME() function is as follows:

SUBTIME(expr1, expr2)

The expr1 parameter can be any valid date, datetime, or time expression, or a string that can be converted to a date, datetime, or time value. The expr2 parameter can be any valid time expression, or a string that can be converted to a time value. If either parameter is NULL, the function returns NULL. The SUBTIME() function returns a date, datetime, or time value that is the result of subtracting the time value expr2 from the date, datetime, or time value expr1. For example, SUBTIME('2023-01-15 10:02:34', '01:00:00') returns ‘2023-01-15 09:02:34’, which is one hour before the given datetime value.

Examples

Let’s see some examples of how to use the SUBTIME() function in MySQL.

Example 1: Subtract a time value from a date value

We can use the SUBTIME() function to subtract a time value from a date value. For example:

SELECT SUBTIME('2023-01-15', '01:00:00') AS result;

This query will subtract one hour from the date value ‘2023-01-15’. The query will return ‘2023-01-14 23:00:00’, which is a datetime value that represents the previous date and time.

Example 2: Subtract a time value from a datetime value

We can use the SUBTIME() function to subtract a time value from a datetime value. For example:

SELECT SUBTIME('2023-01-15 10:02:34', '01:00:00') AS result;

This query will subtract one hour from the datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 09:02:34’, which is a datetime value that represents the same date and time, but one hour earlier.

Example 3: Subtract a time value from another time value

We can use the SUBTIME() function to subtract a time value from another time value. For example:

SELECT SUBTIME('10:02:34', '01:00:00') AS result;

This query will subtract one hour from the time value ‘10:02:34’. The query will return ‘09:02:34’, which is a time value that represents the same time, but one hour earlier.

Example 4: Subtract a fractional time value from a date or datetime value

We can use the SUBTIME() function to subtract a fractional time value from a date or datetime value, such as a half hour, a quarter hour, or a tenth of a second. For example:

SELECT SUBTIME('2023-01-15 10:02:34', '0:0:0.1') AS result;

This query will subtract a tenth of a second from the date or datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 10:02:33.9’, which is a datetime value that represents the same date and time, but a tenth of a second earlier.

Example 5: Subtract a negative time value from a date or datetime value

We can use the SUBTIME() function to subtract a negative time value from a date or datetime value, which is equivalent to adding a positive time value. For example:

SELECT SUBTIME('2023-01-15 10:02:34', '-01:00:00') AS result;

This query will subtract a negative one hour from the date or datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 11:02:34’, which is a datetime value that represents the same date and time, but one hour later.

There are some other functions that are related to the SUBTIME() function, and can be useful for working with dates, datetimes, and times. Here are some of them:

ADDTIME(): This function adds a time value to a date or datetime value, or a time value to another time value. For example, ADDTIME('2023-01-15 10:02:34', '01:00:00') returns ‘2023-01-15 11:02:34’.
DATE_SUB(): This function subtracts a specified interval from a date or datetime value. For example, DATE_SUB('2023-01-15', INTERVAL 1 DAY) returns ‘2023-01-14’.
DATE_ADD(): This function adds a specified interval to a date or datetime value. For example, DATE_ADD('2023-01-15', INTERVAL 1 DAY) returns ‘2023-01-16’.
DATE(): This function returns the date part of a date or datetime value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
TIME(): This function returns the time part of a date or datetime value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.

Conclusion

In this article, we learned how to use the MySQL SUBTIME() function, which subtracts a time value from a date or datetime value, or a time value from another time value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates, datetimes, and times.