...

View Full Version : Checking time/date for older then 60 days



masterofollies
07-22-2008, 02:52 PM
In my database, the postdate for messages is


0000-00-00 00:00:00

On my website it looks like this


2008-07-21 12:17:01

I am setting up a Cron job to delete messages that are older then 60 days. I know the formula is something like 24/60/60 unless thats for time and not a date. In my query I need it to check for older then 60 days. Here is the type of line I am using to update.


mysql_query("UPDATE messages SET id='$deleteid' WHERE postdate>'$postdate'") or die(mysql_error());

Also is there a DELETE code to use instead of SET ?

kbluhm
07-22-2008, 03:22 PM
$postdate = date(
'Y-m-d H:i:s', // 0000-00-00 00:00:00
time() - ( 60 * 60 * 24 * 60 ) // 60 days into the past
);
mysql_query( "DELETE FROM `messages` WHERE `postdate` < '{$postdate}'" ) or exit( mysql_error() );

djm0219
07-22-2008, 03:26 PM
Got it wrong :(

masterofollies
07-22-2008, 04:03 PM
kbluhm, that looks good. So let me try to understand and learn this.

So it's 60 seconds X 60 minutes X 24 hours X 60 days?

But on this one,


WHERE `postdate` < '{$postdate}'

Wouldn't that delete every message that is less then 60 days old?

PeaTearGriffin
07-22-2008, 05:14 PM
I do it a different way, but I don't know if its slower. I grab the UNIX_TIMESTAMP and then compare it to time() + how many extra seconds you need. But that's mostly when one format is already a timestamp.

kbluhm
07-22-2008, 05:26 PM
kbluhm, that looks good. So let me try to understand and learn this.

So it's 60 seconds X 60 minutes X 24 hours X 60 days?

But on this one,


WHERE `postdate` < '{$postdate}'

Wouldn't that delete every message that is less then 60 days old?

No. That will delete all messages older than 60 days, just as you asked. It's simple math really:


// some approximations
$threeDaysAgo = '2008-07-19 00:00:00';
$sixtyDaysAgo = '2008-05-22 00:00:00';
$ninetyDaysAgo = '2008-04-22 00:00:00';

// delete 3 days ago?
( $threeDaysAgo < $sixtyDaysAgo ) // FALSE
( '2008-07-19 00:00:00' < '2008-05-22 00:00:00' ) // FALSE

// delete 90 days ago?
( $ninetyDaysAgo < $sixtyDaysAgo ) // TRUE
( '2008-04-22 00:00:00' < '2008-05-22 00:00:00' ) // TRUE

oesxyl
07-22-2008, 05:27 PM
mysql_query("DELETE FROM `messages`WHERE DATE_SUB(`postdate`,INTERVAL 60 DAY'") or die(mysql_error());

Not tested so use carefully.
date_sub return a date, you must compare with something, let's say now(), today() or current_timestamp.

regards

oesxyl
07-22-2008, 05:30 PM
why do you write a script in php to run daily, I guess, from cron instead of using a trigger in mysql?

regards

masterofollies
07-22-2008, 11:57 PM
Ok I think I get it now, thanks so much.

muzammal
07-23-2008, 10:40 AM
Thank you for starting helpful thread. It really helped me a lot as I am learning PHP programming.


Ok I think I get it now, thanks so much.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum