How to Use the DATE_SUB() Function in MySQL?

The DATE_SUB() function is a powerful tool in MySQL that allows you to take a specific date and subtract a certain amount of time from it. The time to be subtracted can be in units like days, months, or years, depending on what you need. This function is especially helpful while working with date and time information in the MySQL database. It allows us to easily manipulate date-time values to perform various calculations and analyses.

This guide will provide you with a step-by-step guide on using the DATE_SUB() function in MySQL with different examples.

How to Utilize the DATE_SUB() Function in MySQL?

The “DATE_SUB()” is a built-in function in MySQL that can be utilized to subtract the specific time interval (SECOND, MINUTE, HOUR, DAY, etc.) from the given date-time value. To use DATE_SUB() function, first, you need to understand the syntax of this function:

In the above syntax, two arguments are given to the “DATE_SUB()” function:

• The first argument is “[date]”, which is for the given date-time value from which you want to subtract the specific time interval.
• The second argument is specified using the “INTERVAL” keyword used to specify the time interval that you want to subtract from the given date-time.
• The time interval is specified by “[value]” and “[unit]” parameters, where “[value]” represents the numeric value of the time interval and “[unit]” represents the unit of time that you want to subtract (such as WEEK, DAY, HOUR, MINUTE, SECOND, etc.).

Let’s move to the examples of the DATE_SUB() function in MySQL.

Example 1: Subtracting Seconds From a Date

In MySQL, the “DATE_SUB()” function can be utilized to subtract the time interval in seconds, minutes and hours, etc., from the given date-time value. An example of subtracting the time in seconds from the given static date-time value is given below:

In the above example, the “[date]” is “2023-03-15 10:40:30”, the “[value]” is “20” and the “[unit]” is “SECOND”.

Output

The output showed that the 20 seconds from the given date-time has been subtracted.

Example 2: Subtracting a Week From a Date

The “DATE_SUB()” function can also be utilized to subtract a week from the given date-time value as shown in the given below example:

The “WEEK” is being used in place of “[unit]”.

Output

The output depicts that a “week” or “7 days” have been subtracted from the provided date value which was given as an argument to the “DATE_SUB()” function.

Example 3: Subtracting a Month From a Date

To subtract a month using the “DATE_SUB()” function from the given date-time value, you need to use the “MONTH” time interval in place of the “[unit]” as provided below:

Output

The output displayed that a month has been subtracted from the given date value.

Example 4: Subtracting a Year From a Given Current Date

The “DATE_SUB()” function can be utilized with other functions, such as the “CURDATE()” function to retrieve the current date value. To subtract the particular time interval from the current date value, type the given below command:

In the above command, 1 year is subtracted from the current date.

Output

The output showed the current date and the date after applying the DATE_SUB() function.

Example 5: Using the Result of a Query as the Input Date

The “DATE_SUB()” function can be used to manage and analyze the table data by subtracting any time interval from the date-time value. An example of using the “DATE_SUB()” function with the “SELECT” statement to analyze the data is given below:

In the above example, the “DATE_SUB()” function is applied to the “joineddate” column of the “lh_data” table.

Output

The output displayed the data from the “lh_data” table and the result after applying the “DATE_SUB()” function.

Conclusion

The DATE_SUB() function in MySQL is an incredibly useful tool that can help you manage and analyze date-time data efficiently, by subtracting specific time intervals from given date-time values. The syntax for the DATE_SUB() function is straightforward and easy to use. Whether you need to subtract seconds, weeks, months, or years from a date-time value, the DATE_SUB() function is the perfect tool for the job. This post has illustrated a detailed guide on the use of MySQL’s DATE_SUB() function.