MySQL SUBTIME() – Subtract from Time Values in MySQL

By /
SUBTIME Function

In this tutorial, we will study the MySQL SUBTIME() function. Suppose you have a table which contains the names of shops and their opening time. Now suppose it is the summer season and all these shops decide to open an hour early. You have been tasked with subtracting an hour from all the time values.

This situation calls for the use of MySQL SUBTIME() function. The MySQL SUBTIME() function is used to subtract a time interval from a time or datetime value. The function takes two parameters– the first one is the original time/datetime value and the second is the time interval to be subtracted. Let us take a look at the syntax and examples of the MySQL SUBTIME() function.

Recommended read – MySQL ADDTIME()

Syntax of MySQL SUBTIME()

SUBTIME(time, sub_time_val)Code language: SQL (Structured Query Language) (sql)

Where

  • ‘time’ is the time/datetime value to be modified and,
  • ‘sub_time_val’ is the time interval to be subtracted from ‘time’. This value can be positive or negative.

Examples of MySQL SUBTIME()

Let us kick things off with some basic examples. Let us write queries for the following to problems –

  • Subtracting 6:30:14 from 14:30:14, and
  • Subtracting 7:18:12 from 20:40:19

We will use the SELECT statement and an alias called ‘Result’ to make our output readable. The queries are –

SELECT SUBTIME('14:30:14', '6:30:14') AS Result; 
SELECT SUBTIME('20:40:19', '7:18:12') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Subtime Basic Examples

MySQL SUBTIME() With Fractional Seconds Precision

We can subtract time values with fractional seconds precision using the MySQL Subtime() function. Let us see a couple of examples of this. Consider the below queries.

SELECT SUBTIME('18:45:30.450', '8:30:15.220') AS Result; 
SELECT SUBTIME('16:55:27.54334', '12:00:27.696657') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Subtime Fsp

SUBTIME() With Wrong Time Values

MySQL SUBTIME() returns NULL if we pass a wrong time value to it. A wrong time value is one in which the minute or the second value is greater than 59. Let us see an example of this.

SELECT SUBTIME('06:14:03', '15:50:90') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Subtime Wrong Value

MySQL SUBTIME() With CURTIME()

Since CURTIME() is a function that returns a time value, we can pass it to the SUBTIME() function. Let us write a query where we subtract an hour from the current time.

SELECT CURTIME(), SUBTIME(CURTIME(), '01:00:00') AS '-1 Hour'; Code language: SQL (Structured Query Language) (sql)

And the output is –

Subtime Curtime

SUBTIME() With Datetime Values

We can also subtract datetime values using the SUBTIME() function. Consider the below query.

SELECT SUBTIME('2019-05-01 11:15:45', '20 04:02:01') AS Result; Code language: SQL (Structured Query Language) (sql)

In the above query, we have a datetime value – ‘2019-05-01 11:15:45.’ The ‘sub_time_val’ parameter has the value – ’20 04:02:01′. The 20 in that value stands for 20 days while 04:02:01 is the time value. The above query will subtract 20 days, 4 hours 2 minutes and 1 second from the given datetime value. The output is –

Subtime Datetime1

Let us see another example of this. Consider the below query.

SELECT SUBTIME('2019-01-01 01:15:45', '06:30:00') AS Result;Code language: SQL (Structured Query Language) (sql)

Here, the ‘sub_time_val’ parameter has a value that, if subtracted, from the datetime value, will also change the date value. SUBTIME() does this for us. The output is –

Subtime Datetime2

The Limitation of the Time Datatype

One thing you should know while working with the time data type is that it has a major limitation. The time data type is limited to a range from -838:59:59 to 838:59:59. This means it does not recognize values out of that range. Let us see an example where the subtraction of two time values results in a value outside the above range. The query is –

SELECT SUBTIME('950:20:15', '10:00:00') AS Result; Code language: SQL (Structured Query Language) (sql)

And the output is –

Subtime Limitation1

As you can make out, this value is incorrect. We also get a warning. This is because SUBTIME() subtracts 10:00:00 from the time value ‘838:59:59’ as it is the upper limit. Let us prove this using the below query.

SELECT SUBTIME('838:59:59.999999', '10:00:00') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Subtime Limitation2

Working With Tables

Consider the below Shops table.

Subtime Shops

Now let us come back to the problem I mentioned in the beginning. Suppose you have a table which contains the names of shops and their opening time. Now suppose it is the summer season and all these shops decide to open an hour early. You have been tasked with subtracting an hour from all the time values.

Since we are familiar with the SUBTIME() function. Let us use it along with the UPDATE statement. The query is –

UPDATE Shops SET OpeningTime=SUBTIME(OpeningTime, '01:00:00'); 
SELECT * FROM Shops;Code language: SQL (Structured Query Language) (sql)

And the output is –

Subtime Table Example

Conclusion

Basic subtraction operations on time and datetime values are very important and have a plethora of use cases. That is why, the SUBTIME() function is so important. I would encourage you to practice some more queries with this function.

References