Using the MySQL function DATE_SUB

mySQL

SQL logoRecently, I needed to craft a SQL query that would allow me to determine the records that had been stored in the last 30 days. I found DATE_SUB() was just the thing I needed for the query. Assuming you have a record field containing a date, such as a field `submitted,` if you would like to find all records submitted in the last 30 days, you could use the following SQL feature to the WHERE conditional:

AND `submitted` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

DATE_SUB() accepts 2 parameters; the date of interest, and a phrase of the form ‘INTERVAL ii xxxxx.’ DATE_SUB() subtracts the specified interval from the from the date of interest. So, if we want to find what has happened in the last 30 days, subtract the INTERVAL 30 DAY from the current date and then ask for everything after that Date in the query.

For completeness, let’s note:

  • CURDATE() returns the current date in the format yyyy-mm-dd.
  • INTERVAL accepts:
    • A number appropriate for the type of interval you choose,
    • The following interval types
      • MICROSECOND
      • SECOND
      • MINUTE
      • HOUR
      • DAY
      • MONTH
      • QUARTER
      • YEAR
      • and other*

*For more available interval types, see http://www.w3schools.com/sql/func_date_sub.asp

Leave a Reply