MySQL Query Results From Date Range

An article by admin    No Comments

It’s often unnecessary to select EVERYTHING from the beginning of your database table when performing queries, and depending on how much information your database contains- you’re likely displaying more results than anyone cares about!

But alas, there is a better way!

The following queries are very straighforward, specifying that the query should scan your database table date field (‘date_field’) for results that are greater than, or equal to the current date and time (‘NOW()’), and are to go forward or back in time by a specified interval (‘INTERVAL -3 DAY’).

SELECT * FROM your_table WHERE date_field >= DATE_ADD(NOW(), INTERVAL -3 DAY) ORDER BY date_field ASC

The query above functions just like your everyday query, with the exception that the query above only returns results from the last 3 days (instead of since the beginning of time).

We may also use the “DATE_SUB” version to render the same content:

SELECT * FROM your_table WHERE date_field >= DATE_SUB(NOW(), INTERVAL 3 DAY)

You say, “what if I need content that goes back 3 months?”.  No problem, and it isn’t necessary for us to enter a ‘INTERVAL -90’.  There are a number of type values that you can use that are very straightforward such as:

• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• YEAR_MONTH (entered as “Y.M” [1 year, 2 months would be 1.2 YEAR_MONTH])

Play around with your queries and have fun NOT sorting through results from the beginning of time!