{"id":2629,"date":"2018-06-20T21:33:26","date_gmt":"2018-06-21T04:33:26","guid":{"rendered":"https:\/\/database.guide\/?p=2629"},"modified":"2021-01-28T14:11:23","modified_gmt":"2021-01-28T04:11:23","slug":"date_sub-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/date_sub-examples-mysql\/","title":{"rendered":"DATE_SUB() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, you can use the DATE_SUB()<\/code> function to subtract a specified amount of time from a date. For example, you can use it to subtract 7 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, etc.<\/p>\n

This function is similar to\u00a0DATE_ADD()<\/code><\/a>, except that it subtracts from a date instead of adds to it.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

DATE_SUB(date,INTERVAL expr unit)<\/pre>\n
Example 1 – Basic Usage<\/h2>\n
Here’s an example of usage.<\/p>\n
SELECT DATE_SUB('2021-05-07', INTERVAL 5 DAY) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n+------------+\r\n| 2021-05-02 |\r\n+------------+<\/pre>\n
This example subtracts 5 days from the date supplied by the first argument.<\/p>\n
Example 2 – Other Date Units<\/h2>\n
You can specify the units in days, weeks, months, years, etc. Here are some examples.<\/p>\n
SELECT \r\n    '2021-05-07' AS 'Start Date',\r\n    DATE_SUB('2021-05-07', INTERVAL 2 WEEK) AS '-2 Weeks',\r\n    DATE_SUB('2021-05-07', INTERVAL 2 MONTH) AS '-2 Months',\r\n    DATE_SUB('2021-05-07', INTERVAL 2 QUARTER) AS '-2 Quarters',\r\n    DATE_SUB('2021-05-07', INTERVAL 2 YEAR) AS '-2 Years';\r\n<\/pre>\n
Result:<\/p>\n
+------------+------------+------------+-------------+------------+\r\n| Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |\r\n+------------+------------+------------+-------------+------------+\r\n| 2021-05-07 | 2021-04-23 | 2021-03-07 | 2020-11-07  | 2019-05-07 |\r\n+------------+------------+------------+-------------+------------+<\/pre>\n
Example 3 – Time Units<\/h2>\n
You can also subtract time units from a date\/time value. Here’s an example.<\/p>\n
SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL 5 HOUR) AS Result;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+\r\n| Result              |\r\n+---------------------+\r\n| 2021-05-07 05:00:00 |\r\n+---------------------+<\/pre>\n
And you can specify multiple units at the same time. For example, you can specify hours and minutes. Like this.<\/p>\n
SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL '1:30' HOUR_MINUTE) AS Result;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+\r\n| Result              |\r\n+---------------------+\r\n| 2021-05-07 08:30:00 |\r\n+---------------------+<\/pre>\n
Expected Values<\/h2>\n
The following table shows the valid unit values and their expected format.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
unit<\/code> Value<\/th>\nExpected expr<\/code> Format<\/th>\n<\/tr>\n<\/thead>\n
MICROSECOND<\/td>\nMICROSECONDS<\/td>\n<\/tr>\n
SECOND<\/td>\nSECONDS<\/td>\n<\/tr>\n
MINUTE<\/td>\nMINUTES<\/td>\n<\/tr>\n
HOUR<\/td>\nHOURS<\/td>\n<\/tr>\n
DAY<\/td>\nDAYS<\/td>\n<\/tr>\n
WEEK<\/td>\nWEEKS<\/td>\n<\/tr>\n
MONTH<\/td>\nMONTHS<\/td>\n<\/tr>\n
QUARTER<\/td>\nQUARTERS<\/td>\n<\/tr>\n
YEAR<\/td>\nYEARS<\/td>\n<\/tr>\n
SECOND_MICROSECOND<\/td>\n‘SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
MINUTE_MICROSECOND<\/td>\n‘MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
MINUTE_SECOND<\/td>\n‘MINUTES:SECONDS’<\/td>\n<\/tr>\n
HOUR_MICROSECOND<\/td>\n‘HOURS:MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
HOUR_SECOND<\/td>\n‘HOURS:MINUTES:SECONDS’<\/td>\n<\/tr>\n
HOUR_MINUTE<\/td>\n‘HOURS:MINUTES’<\/td>\n<\/tr>\n
DAY_MICROSECOND<\/td>\n‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
DAY_SECOND<\/td>\n‘DAYS HOURS:MINUTES:SECONDS’<\/td>\n<\/tr>\n
DAY_MINUTE<\/td>\n‘DAYS HOURS:MINUTES’<\/td>\n<\/tr>\n
DAY_HOUR<\/td>\n‘DAYS HOURS’<\/td>\n<\/tr>\n
YEAR_MONTH<\/td>\n‘YEARS-MONTHS’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You can also use the\u00a0SUBDATE()<\/code> function to do the same thing (it’s a synonym for the DATE_SUB()<\/code> function when using the same syntax).<\/p>\n
In addition, the syntax of SUBDATE()<\/code>\u00a0has a second form, which is a shorthand method of subtracting a certain number of days from a date. For more info, see SUBDATE() Examples in MySQL<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MySQL, you can use the DATE_SUB() function to subtract a specified amount of time from a date. For example, you can use it to subtract 7 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2629","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2629","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2629"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2629\/revisions"}],"predecessor-version":[{"id":2633,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2629\/revisions\/2633"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}