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 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jan 2009
    Posts
    204
    Thanks
    29
    Thanked 0 Times in 0 Posts

    Delete HOUR or DAY ?

    I need to delete all where timestamp is over 35hrs old, as the links are then void..
    I have tried
    $query = "DELETE FROM import WHERE Timestamp < NOW() - INTERVAL 35 HOUR";
    But it seems to delete all over 24hrs old

    When I use
    $query = "DELETE FROM import WHERE Timestamp < NOW() - INTERVAL 3 DAY";
    The older links are dead.

    Any ideas
    Last edited by Feckie; 07-21-2012 at 02:13 PM.

  • #2
    New Coder
    Join Date
    Sep 2011
    Posts
    80
    Thanks
    0
    Thanked 13 Times in 12 Posts
    Off the top of my head, date_add any good?

    Code:
    WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()

  • Users who have thanked MarkR for this post:

    Feckie (07-21-2012)

  • #3
    Regular Coder
    Join Date
    Jan 2009
    Posts
    204
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkR View Post
    Off the top of my head, date_add any good?

    Code:
    WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()
    That Adds or subtracts a specified time interval from a date, what i want to do,
    is Delete from database files older than 35 hours

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Have you tried doing a SELECT * FROM import where Timestamp < NOW() - INTERVAL 35 HOUR? It looks like it might be a timezone issue. Also try just a SELECT NOW() - INTERVAL 35 HOUR, and make sure it looks good.

  • Users who have thanked Keleth for this post:

    Feckie (07-21-2012)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Mark's answer is correct.

    He was only showing the WHERE part of the query.

    The full query would be
    Code:
    DELETE FROM import WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()
    But Keleth's answer should have worked, too.

    Part of your problem may be that timestamp is a reserved word in MySQL. You really should try to avoid using reserved words as field and table names.

    If that is the problem, though, you can just enclosed the name in BACKTICKS -- the ` character that shares a key with the ~ tilde.
    Code:
    DELETE FROM import WHERE DATE_ADD(`Timestamp`, INTERVAL 35 HOUR) < NOW()
    or
    Code:
    DELETE FROM import WHERE `timestamp` < NOW() - INTERVAL 35 HOUR
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Feckie (07-21-2012)

  • #6
    Regular Coder
    Join Date
    Jan 2009
    Posts
    204
    Thanks
    29
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    I have just to admit what a plonker I am..

    mindset 1 day = 12 hrs Must be getting older than I thought

    now set to 3 days = 72hrs so set to delete to 71hrs and it's working

    Go have a good laugh... :-)
    Last edited by Feckie; 07-21-2012 at 02:12 PM.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Can you show us some sample rows from your table and which ones are being deleted that shouldn't be? Something doesn't seem right and it isn't the code above.

    the DATE_ADD as written takes all dates and adds 35 hours to them. If the dates are within 35 hours of now(), adding the 35 hours will bring them to a value greater than now() and thus would fail the WHERE clause and not be deleted.

  • Users who have thanked guelphdad for this post:

    Feckie (07-21-2012)

  • #8
    Regular Coder
    Join Date
    Jan 2009
    Posts
    204
    Thanks
    29
    Thanked 0 Times in 0 Posts

    Smile

    Quote Originally Posted by guelphdad View Post
    Can you show us some sample rows from your table and which ones are being deleted that shouldn't be? Something doesn't seem right and it isn't the code above.

    the DATE_ADD as written takes all dates and adds 35 hours to them. If the dates are within 35 hours of now(), adding the 35 hours will bring them to a value greater than now() and thus would fail the WHERE clause and not be deleted.
    ha ha read above


  •  

    Posting Permissions

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