MySQL – Subtract Time Value (Intervals) From Date

MySQL DATE_SUB() function allow you to substract a time value (Intervals) from the selected date.

MySQL SUBDATE() function is the synonym of DATE_SUB() function.

The following examples will return a date after substracting 10 days from the specified date 2014-01-15.

Syntax – DATE_SUB() Function

DATE_SUB(date,INTERVAL expr unit);

Example:

mysql> SELECT DATE_SUB('2014-01-15', INTERVAL 10 DAY);

Output:

'2014-01-10'

Syntax – SUBDATE() Function

SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days);

Example-1:

mysql> SELECT SUBDATE('2014-01-15', INTERVAL 10 DAY);

Output:

'2014-01-10'

Example-2:

mysql> SELECT SUBDATE('2014-01-15', 10);

Output:

'2014-01-10'

Arguments

Name Description
date Date or datetime value
INTERVAL Keyword
expr An expression specifying the interval value
unit Keyword indicating the units

The following table shows the expected form of the expr argument for each unit value.

unit Value Expected expr 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'

1 Comment on “MySQL – Subtract Time Value (Intervals) From Date

Leave a Reply

Your email address will not be published.

*