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 ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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.

Be First to Comment

Leave a Reply

Your email address will not be published.