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
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)