2

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.

1

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());
1

Could you not use native parameters for this?

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

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
New contributor
imars is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.