query in MySQL

asolell

Hi Y'all,

This is not really a PHP question, it is rather a SQL question, but I didn't know where else to look for answers:

In Mysql I need to build a query that does the following:

Keep ONLY the last two weeks of data.
Erase everthing before the last two weeks.

Isn't it simple?

The post_date is a date field (unix) in seconds.
Two weeks: 14246060 = 1209600

(in case you are wondering,. this is an Invision Board forum database).

I thought this is the right syntax/logic, but it didn't work:

DELETE from ibf_posts where now() - post_date > 1209600;

I've tried this query and it didn't do the trick, so I may be off with the
logic.
So I tried all sorts of permutations with not much luck.

DELETE from ibf_posts where (post_date > now() - 1209600)
DELETE from ibf_posts where (post_date - now()) > 14246060
DELETE from ibf_posts where (now() - post_date) > 14246060
DELETE from ibf_posts where (now() - 14246060 ) < (now() -
14246060 ) ;

I got new messages deleted, all messages deleted, anything but what I
needed.

If any of you can come up with the proper logic, I would appreciate it very much.
Thanks,

-Alon.

devinemke

i assume from your post that post_date is an integer field storing a unix timestamp? if so:

DELETE FROM ibf_posts WHERE post_date < (UNIX_TIMESTAMP() - 1209600)

Drakla

Other thing to note about devinemke's solution is that he does all the maths on the one side of the < symbol, which means you're comparing the post date against a constant calculated before the query begins, so if you've got an index on the post date field it will be used.

If it had been done as "post_date + 1209600 > UNIX_TIMESTAMP()" then every field in the table will be checked and all the extra calculations done.

asolell

Originally posted by devinemke
i assume from your post that post_date is an integer field storing a unix timestamp? if so:
DELETE FROM ibf_posts WHERE post_date < (UNIX_TIMESTAMP() - 1209600)
[/B]

I have seen the light, and it is good!

Thanks a lot.
I missed on the UNIX_TIMESTAMP() as a whole. That's such simple thingy,.. yet without it,.. there wasn't much that I was able to do.

You are a lifesaver! (do you come in a 3PK?) 😃 😃

Thanks again.

-Alon.