MySQL SUBDATE() – Subtract from DateTime Values in MySQL

By /
SUBDATE Function

In this tutorial, we will study the MySQL SUBDATE() function. Suppose you are a HR manager and you notice that an intern who you tasked with filling up an Employee table in MySQL filled up wrong joining dates of all employees from the Executive department. He added a year to the original joining dates of all employees in the Executive department. It is your job to rectify it now! This is where we can use the MySQL SUBDATE() function.

The SUBDATE() function is used to subtract a date or time interval from a date value. Let us take a look at its syntax, followed by some examples.

Recommended read – MySQL ADDDATE()

Syntax of MySQL SUBDATE()

SUBDATE(date, days)Code language: SQL (Structured Query Language) (sql)

Or,

SUBDATE(date, INTERVAL value interval_type)Code language: SQL (Structured Query Language) (sql)

Where,

  • ‘date’ is the date/datetime value to be modified, 
  • ‘days’ is the number of days to be added to ‘date’,
  • ‘value’ is the value of the time interval to be added and,
  • ‘interval_type’ is the type of interval to be added.

The ‘interval_type’ parameter can have the below values – 

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

The below ‘interval_type’ values should be in quotes.

  • SECOND_MICROSECOND where value is of the format – ‘SECONDS.MICROSECONDS’
  • MINUTE_MICROSECOND where value is of the format – ‘MINUTES:SECONDS.MICROSECONDS’
  • MINUTE_SECOND where value is of the format – ‘MINUTES:SECONDS’
  • HOUR_MICROSECOND where value is of the format – ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
  • HOUR_SECOND where value is of the format – ‘HOURS:MINUTES:SECONDS’
  • HOUR_MINUTE where value is of the format – ‘HOURS:MINUTES’
  • DAY_MICROSECOND where value is of the format – ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
  • DAY_SECOND where value is of the format – ‘DAYS HOURS:MINUTES:SECONDS’
  • DAY_MINUTE where value is of the format – ‘DAYS HOURS:MINUTES’
  • DAY_HOUR where value is of the format – ‘DAYS HOURS’
  • YEAR_MONTH where value is of the format – ‘YEARS-MONTHS’

Examples of MySQL SUBDATE()

Let us kick things off with a few basic examples of MySQL SUBDATE().

Let us see an example of SUBDATE() with the first syntax I mentioned in the syntax section. Consider the below queries. In the first query we subtract 15 days from the given date value. In the second query, we subtract 10 days from the given date value. We will use the SELECT statement and aliases to make our output readable.

SELECT SUBDATE('2021-05-30', 15) AS Result; 
SELECT SUBDATE('2021-02-10', 10) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL SUBDATE Syntax1

Now, let us see examples of MySQL SUBDATE() with the 2nd syntax. Consider the below queries. In the first query we subtract 1 year from the given date. In the second query, we subtract 45 days from the given date.

SELECT SUBDATE('2021-05-30', INTERVAL 1 YEAR) AS Result; 
SELECT SUBDATE('2021-05-30', INTERVAL 45 DAY) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL SUBDATE Syntax2

MySQL SUBDATE() With CURDATE()

Let us see an example of SUBDATE() with the CURDATE() function. We will write a query that will subtract 2 days, 2 weeks, 2 months, 2 quarters and 2 years from the current date and display each result separately in each column in the result-set. The query is –

SELECT CURDATE() AS Today, 
SUBDATE(CURDATE(), INTERVAL 2 DAY) AS '-2 Days', 
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';Code language: SQL (Structured Query Language) (sql)

And the output is –

Subdate Curdate

MySQL SUBDATE() With Datetime Values Using the NOW() function

As I mentioned in the syntax section, we can also use datetime values with the SUBDATE() function. Since the NOW() function also returns datetime values, let us write a query using it. Let us subtract 45 microseconds, 45 seconds, 45 minutes, 45 hours, and 12 hours 30 minutes from the result returned by the NOW() function. We will display each difference in a separate column in the result-set. The query is –

SELECT NOW() AS 'Current Time', 
SUBDATE(NOW(), INTERVAL 45 MICROSECOND) AS '-45 Microseconds', 
SUBDATE(NOW(), INTERVAL 45 SECOND) AS '-45 Seconds', 
SUBDATE(NOW(), INTERVAL 45 MINUTE) AS '-45 Minutes', 
SUBDATE(NOW(), INTERVAL 45 HOUR) AS '-45 Hours', 
SUBDATE(NOW(), INTERVAL '12:30' HOUR_MINUTE) AS '-12 Hours and 30 Minutes';Code language: SQL (Structured Query Language) (sql)

And the output is –

Subdate Now

Adding using MySQL SUBDATE()

Yes, you heard that right! We can add the date using the SUBDATE() function if we pass a negative number as the ‘value’ parameter. Consider the below queries.

SELECT SUBDATE('2021-05-30', INTERVAL -5 YEAR) AS Result; 
SELECT SUBDATE('2021-03-20 08:40:25', INTERVAL '-5 2' DAY_HOUR) AS Result;Code language: SQL (Structured Query Language) (sql)

In the first query, we pass -5 as the year value. This will in turn, add 5 years to the given date. In the second query, we pass -5 as the day value and 2 as the hour value. This will subtract 5 days from the given date but add 2 hours to the given time in the datetime value. The output is –

Subdate Addition

Working With Tables

Consider the below Employee table.

Adddate Employee Table
Employee Table

Now let us tackle the problem I spoke of earlier. Suppose you are a HR manager and you notice that an intern who you tasked with filling up an Employee table in MySQL filled up wrong joining dates of all employees from the Executive department. He added a year to the original joining dates of all employees in the Executive department.

Now that you know how to use the SUBDATE() function, let us use it with the UPDATE statement and the WHERE clause to solve the above problem. Consider the below queries.

UPDATE Employee SET Date_Joined=SUBDATE(Date_Joined, INTERVAL 1 YEAR) WHERE Department="Executive"; 
SELECT * FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Subdate Table Example

Conclusion

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

References