Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts

    Checking time/date for older then 60 days

    In my database, the postdate for messages is

    Code:
    0000-00-00 00:00:00
    On my website it looks like this

    Code:
    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.

    Code:
    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 ?

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    PHP Code:
    $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() ); 
    Last edited by kbluhm; 07-22-2008 at 03:28 PM.

  • #3
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Got it wrong
    Last edited by djm0219; 07-22-2008 at 05:38 PM.
    Dave .... HostMonster for all of your hosting needs

  • #4
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    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,

    Code:
    WHERE `postdate` < '{$postdate}'
    Wouldn't that delete every message that is less then 60 days old?

  • #5
    Regular Coder
    Join Date
    Apr 2006
    Posts
    186
    Thanks
    21
    Thanked 6 Times in 6 Posts
    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.

  • #6
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Quote Originally Posted by masterofollies View Post
    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,

    Code:
    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:
    PHP Code:
    // 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 
    Last edited by kbluhm; 07-22-2008 at 05:36 PM.

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by djm0219 View Post
    Code:
    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

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    why do you write a script in php to run daily, I guess, from cron instead of using a trigger in mysql?

    regards

  • #9
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Ok I think I get it now, thanks so much.

  • #10
    New Coder
    Join Date
    Dec 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for starting helpful thread. It really helped me a lot as I am learning PHP programming.

    Quote Originally Posted by masterofollies View Post
    Ok I think I get it now, thanks so much.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •