DATE_SUB can be very handy if you’re trying to return rows from a database table relative to a date range.

As the name suggests, it SUBtracts a time value from a DATE. It expects a date, as well as an INTERVAL argument consisting of a unit (such as DAY, WEEK, or YEAR) and quantity, like so:

DATE_SUB(date, INTERVAL quantity unit)

And while you can provide any datetime value for date, DATE_SUB is at its most useful when this is set to CURDATE(), as the interval is then subtracted from today’s date. This is the basis for any “older than” SELECT commands you might want to make. To wit:

select * from comments where date_created <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

This will return all comments that were created a year or more ago.

It’s worth noting that unit remains singular even if quantity is greater than one. If, for example, we wanted to return all comments that were created two or more years ago, we would use this:

select * from comments where date_created <= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)

Returning data within the range (as opposed to outside of it) is as simple as reversing the comparison operator (hence the word “relative” in the post title). Want all the comments posted within the last 6 months?

select * from comments where date_created > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)