MySQL Subtract Day, Hours, Minutes from DateTime

MySQL SUBDATE(): function; In this tutorial, i am going to show you how to subtract days, week, month, year, minutes, hours using MySQL SUBDATE() function.

MySQL SUBDATE() Function

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

Syntax of MySQL SUBDATE() Function

The basic syntax of MySQL SUBDATE() function is:

SUBDATE(date,INTERVAL expr unit)

Example 1 – MySQL Subtract Days from DateTime

Let’s take an first simple example using MySQL subdate() function; as follows:

SELECT SUBDATE('2019-07-23', INTERVAL 6 DAY) AS Result;

Output-1

+------------+
| Result     |
+------------+
| 2019-07-17 |
+------------+

Example 2 – MySQL Subtract Week, Month, Year from DateTime

Let’s take an example to subtract the days, weeks, months, years’ in the current date using Mysql subdate() function; as follows:

 SELECT 
 CURDATE() AS 'Start Date',
 SUBDATE(CURDATE(), INTERVAL 2 WEEK) AS '-2 Weeks',
 SUBDATE(CURDATE(), INTERVAL 2 MONTH) AS '-2 Months',
 SUBDATE(CURDATE(), INTERVAL 2 QUARTER) AS '-2 Quarters',
 SUBDATE(CURDATE(), INTERVAL 2 YEAR) AS '-2 Years'; 

Output-2

 +------------+------------+------------+-------------+------------+
 | Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
 +------------+------------+------------+-------------+------------+
 | 2019-07-23 | 2019-07-09 | 2019-05-23 | 2019-01-23  |2017-07-23  |
 +------------+------------+------------+-------------+------------+

Example 3 – MySQL Subtract Hours from DateTime

Let’s subtract the time units from the given date/time value using the MySQL SUBDATE() function; as follows:

SELECT SUBDATE('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result,
       SUBDATE(NOW(), INTERVAL 5 HOUR) AS NOW;

Output-3

 +---------------------+---------------------+
 | Result              | NOW                 |
 +---------------------+---------------------+
 | 2019-07-23 06:00:00 | 2019-07-22 22:04:40 |
 +---------------------+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’

Conclusion

MySQL Subtract Days from DateTime tutorial, you have learned how to use MySQL SUBDATE() function with various examples.

Recommended MySQL Tutorials