MySQL

295 guides

keyboard_arrow_down

Other math topics

Dagster

Pandas

NumPy

Matplotlib

PySpark

MySQL

chevron_leftDate and Time

Method ADDDATE Method ADDTIME Method CONVERT_TZ Method CURDATE Method CURRENT_DATE Method CURRENT_TIME Method CURRENT_TIMESTAMP Method CURTIME Method DATE Method DATE_ADD Method DATE_FORMAT Method DATE_SUB Method DATEDIFF Method DAY Method DAYNAME Method DAYOFMONTH Method DAYOFWEEK Method DAYOFYEAR Method EXTRACT Method FROM_DAYS Method FROM_UNIXTIME Method GET_FORMAT Method HOUR Method LAST_DAY Method LOCALTIME Method LOCALTIMESTAMP Method MAKEDATE Method MAKETIME Method MICROSECOND Method MINUTE Method MONTH Method MONTHNAME Method NOW Method PERIOD_ADD Method PERIOD_DIFF Method QUARTER Method SEC_TO_TIME Method SECOND Method STR_TO_DATE Method SUBDATE Method SUBTIME Method SYSDATE Method TIME Method TIME_FORMAT Method TIME_TO_SEC Method TIMEDIFF Method TIMESTAMP Method TIMESTAMPADD Method TIMESTAMPDIFF Method TO_DAYS Method TO_SECONDS Method UNIX_TIMESTAMP Method UTC_DATE Method UTC_TIME Method UTC_TIMESTAMP Method WEEK Method WEEKDAY Method WEEKOFYEAR Method YEAR Method YEARWEEK

check_circle

Mark as learned

thumb_up

thumb_down

chat_bubble_outline

Comment

auto_stories Bi-column layout

settings

MySQL | DATE_SUB method

schedule Aug 12, 2023

Last updated

local_offer

MySQL

Syntax


        
        
            
                
                
                    SELECT DATE_SUB(date, INTERVAL expr unit);

Parameters

1. date | date/datetime

The date / datetime to subtract the time interval from.

2. expr unit | integer with units

The interval to subtract from the provided date together with its units.

Refer to 'List of units' at the bottom of page for a valid list of units.

Return value

The result of subtracting the specified interval from date.

Examples

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

Basic usage

To subtract 2 days from student enrollment dates:


        
        
            
                
                
                    SELECT fname, day_enrolled, DATE_SUB(day_enrolled, INTERVAL 2 DAY)
FROM students;
                
            
            +----------+--------------+----------------------------------------+
| fname    | day_enrolled | DATE_SUB(day_enrolled, INTERVAL 2 DAY) |
+----------+--------------+----------------------------------------+
| Sky      | 2015-12-03   | 2015-12-01                             |
| Ben      | 2016-04-20   | 2016-04-18                             |
| Travis   | 2018-08-14   | 2018-08-12                             |
| Arthur   | 2016-04-01   | 2016-03-30                             |
| Benjamin | 2014-01-01   | 2013-12-30                             |
+----------+--------------+----------------------------------------+

To subtract 2 months from student enrollment dates:


        
        
            
                
                
                    SELECT fname, day_enrolled, DATE_SUB(day_enrolled, INTERVAL 2 MONTH)
FROM students;
                
            
            +----------+--------------+------------------------------------------+
| fname    | day_enrolled | DATE_SUB(day_enrolled, INTERVAL 2 MONTH) |
+----------+--------------+------------------------------------------+
| Sky      | 2015-12-03   | 2015-10-03                               |
| Ben      | 2016-04-20   | 2016-02-20                               |
| Travis   | 2018-08-14   | 2018-06-14                               |
| Arthur   | 2016-04-01   | 2016-02-01                               |
| Benjamin | 2014-01-01   | 2013-11-01                               |
+----------+--------------+------------------------------------------+

Negative intervals

To add 1 day to a date:


        
        
            
                
                
                    SELECT DATE_SUB('2020-01-01', INTERVAL -1 DAY);
                
            
            +-----------------------------------------+
| DATE_SUB('2020-01-01', INTERVAL -1 DAY) |
+-----------------------------------------+
| 2020-01-02                              |
+-----------------------------------------+

Note that there is a separate method DATE_ADD(~) specifically for adding intervals to a date / datetime.

List of units

The below table represents a list of units that are supported:

Unit	Expected Input 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'`

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

Official MySQL Documentation

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-sub

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!