Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    Walsall - UK
    Posts
    177

    Retrieving data from a database according to a set time period? (PHP/mySQL)

    Hey guys...

    I am entering dates into a mySQL database using PHP "now()". It's stored in the mySQL database as "datetime 0000-00-00 00:00:00"..

    Is there a way, of making a mySQL query to pull out records in that table after a year has passed according to the date entered?

    Thanks in advance

    crE
    <<< Please see Forum Guidelines for signature setup. >>>

  2. #2
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    4,695
    try this:

    Code:
    DELETE FROM table WHERE DATE_SUB(now(),INTERVAL 1 YEAR);
    I think date_sub was only added in MySQL 4.1.something, so this is no good if you're still using 3.x
    Last edited by Ash; 03-26-2004 at 09:28 AM.

  3. #3
    Join Date
    Aug 2003
    Location
    Walsall - UK
    Posts
    177
    Right... I have 4.0 something.... are there any other possible ways to solve this?

    crE
    <<< Please see Forum Guidelines for signature setup. >>>

  4. #4

    With 4.0

    DELETE FROM table WHERE datefield < now() - interval 365 day;

    I do this all the time. No correction for leapyears, though

  5. #5
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    4,695
    bah i shouldn't post when half asleep, what i posted should be

    Code:
    DELETE FROM table WHERE date_column < DATE_SUB(now(),INTERVAL 1 YEAR);
    I do this all the time. No correction for leapyears, though
    You can use "interval 1 year" rather than 365 day in that statment also, then you wont have the leap year problem.

Posting Permissions

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