CODEWITHSUNDEEP
mysqlsqltimestamp
Ben Paton
Ben Paton

Reputation: 1442

Using MySQL to select data from the last three months

I have a table which contains the following columns:

And I'm trying to write an SQL statement which selects the ProductId's which have the highest number of 5 star ratings also with an average rating of 5 and the last review submission was within the last three months.

The table looks like this example:

ID  ProductId   rate1   rate2   rate3   rate4   rate5   aveRate     lastSubDate
18  9996637     0   0   0   0   1   5   2011-08-10 12:00:34
26  9996628     1   0   0   0   0   1   2010-05-06 05:45:05
34  9996618     0   0   0   1   0   4   2011-10-09 09:00:45
36  9996614     5   0   0   0   0   1   2011-01-05 09:30:32
48  9996592     5   0   1   0   3   3   2012-11-28 19:00:06
66  9996566     0   0   0   1   3   5   2011-04-06 06:45:34
70  9996562     0   0   0   1   1   5   2011-05-17 18:30:03

This is the query I've got so far:

SELECT `ProductId`,`rate5`,`aveRate`,`lastSubDate` 
FROM ratings 
WHERE `aveRate` = 5 AND `lastSubDate` > '24 Feb 2013' 
ORDER BY `rate5` DESC 
LIMIT 3

This returns the products with the most 5 star reviews which also have an average rating of 5, however it doesn't limit the results to the last three months as I want. How can I amend this statement to also select data from only the last three months?

Upvotes: 1

Views: 4294

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Your date constant is in the wrong format. Try this if you're using a constant date:

... AND `lastSubDate` > '20130224'

You can also use 20130224 (without the quotes) or '2013-02-24' above - see the MySQL Date and Time Literals documentation.

Or to calculate "three months ago" just do this:

... AND `lastSubDate` > CURDATE() - INTERVAL 3 MONTH

Finally, you don't need the backticks here. If you find them distracting you can drop them:

... AND lastSubDate > whatever

Upvotes: 10

Related Questions