For clean-up purposes of removing drafts I would like to select all posts older than 7 days then delete them. I read about Date_Sub but it doesn’t seem to be giving me the results I would like as it is returning all of them.
(“SELECT post_id FROM posts WHERE post_status = ‘auto-draft’ AND DATE_SUB( NOW(), INTERVAL 7 DAY ) > date_created” )
My dates are stored in unix time so I’m not sure if Date_Sub won’t work with that? The websites I’ve looked at aren’t very clear on its usage. How would I go about this?
Thank you for the quick reply. This does not seem to be returning any results at all however. I even tried reducing the Interval to 1 Minute but it isn’t returning any rows.
Just to clarify and make sure I’m providing the right information.
The date’s are like this 1320250921 and are stored as an 11 digit long int.
I’ve tried running it in the myAdmin but it also returns 0 rows there, but there are quite a few rows that could be returned.
EDIT: I’m not sure what the difference is but using GUIDO’s code in myAdmin seems to be working. Thanks to you both.
If I change it to current_date it doesn’t return anything. I’m starting to get lost here, perhaps I am storing the date wrong?
It’s set to just use time() unless a date is specified in which case it uses strtotime() to convert it.
So for instance I’m using this now in myAdmin
SELECT post_id
FROM posts
WHERE post_status = 'auto-draft'
AND UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 1
MINUTE ) ) > date_created
This returns 29 rows, the earliest date being:
Wed, 02 Nov 2011 14:42:43 GMT
And the latest:
Wed, 02 Nov 2011 15:26:12 GMT
But doesn’t return the next post along:
Wed, 02 Nov 2011 15:26:13 GMT
That’s only .1 second older and the latest post I created was made only 20 minutes ago, so they should all be showing up under there. Is this an issue with using NOW()?
I assume nothing is showing up under current_date because it is using the date not the immediate time?
SELECT MIN(date_created) as mini
, MAX(date_created) as maxi
, UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 7 DAY ) ) AS humpty
, UNIX_TIMESTAMP( CURRENT_DATE - INTERVAL 7 DAY ) AS dumpty
FROM posts
WHERE post_status = 'auto-draft'
Yes, I changed the query to 1 day and it returned the 1 row I specifically altered to be 1 day old. I’m not quite clearl on why using NOW() - 1 minute doesn’t return those older posts but I willl use Current_date as this seems to work fine now I understand what is happening.