Get all entries where expiration date is within last 12 months

Question

I know this is more of a SQL question, but I'm having trouble getting EE to cooperate. I have entries that expire, and want to show only entries which have expired in the last 12 months. I tried:

 SELECT entry_id, title, expiration_date
 FROM exp_channel_titles
 WHERE expiration_date BETWEEN date_sub(Now(), INTERVAL 12 MONTH) AND Now();

I get zero results.

Answer 1

Could you not use native parameters for this?

{exp:channel:entries channel="mychannel" show_expired="only" start_on="-12 months"}

Answer 2

As far as I can tell, you must use use Unix Timestamp (ugh) to get proper results.

So:

SELECT entry_id, title, expiration_date
FROM exp_channel_titles
WHERE expiration_date 
BETWEEN 
  unix_timestamp(date_sub(Now(), INTERVAL 12 MONTH)) 
AND 
  unix_timestamp(Now());

Answer 3

For the datetime fields here is a small change, on the other hand it does not return exactly one year it takes 11 months to have exactly one year of data

  SELECT entry_id, title, expiration_date
    FROM exp_channel_titles
    WHERE expiration_date 
    BETWEEN 
      date_sub(Now(), INTERVAL 11 MONTH)
    AND 
      Now()
    ORDER BY expiration_date DESC