mysql query - show last month's data unix timestamp

Question

I have made this query and it works correct to show data for a month from the current date. For example this query:

SELECT * 
  FROM test 
 WHERE price >=100 
  AND active = 1 
  AND dateadded >= UNIX_TIMESTAMP(DATE_SUB( CURDATE(), INTERVAL 1 MONTH)) 
ORDER BY testvalue DESC

This query outputs date time stamp from i.e.30th October till the current date 30th November. I have edited the query and takeout CURDATE but it fails at the point.

The question is how would I change this query to just show only last months i.e. October's data if the month is November.

Answer 1

You have to get the range between the first day of the previous month, which you can get by formatting the date and setting the day part to 01 and the last day of the previous month. There's a function for that. On the same page in the manual you also find information about DATE_FORMAT().

SELECT * 
  FROM test 
 WHERE price >=100 
  AND active = 1 
  AND dateadded BETWEEN 
    UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB( CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')) 
    AND UNIX_TIMESTAMP(CONCAT(LAST_DAY(DATE_SUB( CURDATE(), INTERVAL 1 MONTH), ' 23:59:59'))
ORDER BY testvalue DESC