Return rows older than a certain date?

Databases
1

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?

2

there’s your problem – integers and dates don’t mix, not without converting one of them to the other

and the trick here is ~not~ to convert the column value

SELECT post_id 
  FROM posts 
 WHERE post_status = 'auto-draft' 
   AND date_created < UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)

:slight_smile:

3

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp


UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 7 DAY )) > date_created

Edit: I’m getting slow… :lol:

4

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.

5

another point –

i’ve seen many people write queries to retrieve data based on “the last X days”…

… and then use NOW() instead of CURRENT_DATE

can anyone see why that’s probably wrong?

6

not in the sample data display that you posted

the values would have to be less than 1319688000

7

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?

8

please humour me and run the following query

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'
9

mini - 1320164320
maxi - 1320252864
humpty- 1319648335
dumpty - 1319605200

10

do you understand what you just ran?

the “mini” value says that no rows will satisfy your “7 days ago” query

11

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.

Thanks for the help.