SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting records older than X days

    Any idea why this does not work:

    Code:
    DELETE FROM shortlist WHERE date_added = DATE_SUB(now(), INTERVAL 5 DAY)
    The table name is "shortlist" (as you'd expect from the above) and the date_added field contains dates in the default format of YYYY-MM-DD

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can you explain what "doesn't work" means?

    what is the datatype of the date_added field?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not deleting records older than 5 days old.

    The datatype = "date".

    Cheers

  4. #4
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well you're using a = operator, I'm not sure how mySQL date fields work but you're only deleting records exactly 5 days old :|
    michael.Crabbe

  5. #5
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course - what an idiot. Thanks CrabbyX.

  6. #6
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK... Tried "<=" & "=>" and it still does not work. How do you do "older than" in MySQL?

  7. #7
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not too sure, but I have just found this example in the mySQL manual:

    SELECT something FROM tbl_name
    WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
    michael.Crabbe

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    crabbyx, you were doing so great...

    that example you showed would select all rows less than 30 days old

    themissingelf, to delete rows older than 5 days, please try this --

    ... WHERE date_added < DATE_SUB(now(), INTERVAL 5 DAY)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aye!

    me = lazy :\
    michael.Crabbe

  10. #10
    SitePoint Member
    Join Date
    Mar 2005
    Location
    New Jersey in the US of A
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why doesnt this work
    $timepost = date("l dS of F Y H:i:s");
    $sql = "DELETE FROM plog WHERE logtime < DATE_SUB(\"$timepost\", INTERVAL 2 DAY)";

    all my logtimes are formatted. is it possible to delete them in this way?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    DATE_SUB works on date or datetime values

    if you feed that query to mysql, then as far as mysql is concerned, that date string is just a string of meaningless characters until it has parsed them to see if they're a valid date

    mysql insists that valid date and datetime values be in year-month-day sequence

    therefore if that php string is not a valid mysql date, it'll get a default zero date value

    you probably just don't have any logfile rows with a date that's 2 days earlier than zero

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Mar 2005
    Location
    New Jersey in the US of A
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql = "DELETE FROM plog WHERE logtime < DATE_SUB(now(), INTERVAL 5 DAY)";
    so now() will select the time right 'now' and I need to put raw time into the sql?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that statement is the best approach, and it will work fine

    it wil delete anything older than 5 days ago from this time today

    no need to change it to anything else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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