The easiest thing would be to write a script to convert all of the dates into a DATETIME field in SQL. If you have access to ALL of the code that modifies this table then go through all of your queries and replace the inserts/updates to work with the DATETIME field in SQL. If you can't do that, then add a DATETIME field at the end of the table and then before you do your processing of the table check to see if there are any empties in the new DATETIME field and then run the conversion thing to fix that. After you have gotten it to a DATETIME field the process of getting the correct rows is fairly trivial with a query like "SELECT * FROM table where column > '$yesterday'" and $yesterday=mktime(...); I believe there is something similar to getting the dates you need at php.net/mktime
If you can't modify the table at all, then your best bet would be something like this:
(use date("h") or whatever to get the correct variables. I don't have any of my code available to me so I can't look anything up :/)
$query="SELECT * FROM table WHERE (1=1)";
//get yesterday's times
for ($a=$hour+1;$a<24;$a++) {
$b=($a<10)?$a:"0".$a;
$query .= " OR left(column,12) = '$yesterdayday $yesterdaymonth $yesterdaydate $b'
}
for ($a=$minute+1;$a<24;$a++) {
$b=($a<10)?$a:"0".$a;
$query .= " OR left(column,14) = '$yesterdayday $yesterdaymonth $yesterdaydate $hour:$b'
}
//get today's times
$query .= " OR left(column,9) = '$day $month $date'
}
That should be accurate to the minute. Make sure to use the padded hour and minute. If you want to extract that method to use for the 7 days, then you'll have to do that yourself. Basically the strategy is to pick off as many as you can with each extra condition as you can. If you think of it as a number like 29382, and you want to get all numbers >23403 (yesterday, or 7 days ago for example), then instead of checking if it's every number from 23403 to 31023 (now) by doing a loop for a = 23403 to 29382 do if number = 29382 then say "found it", it's easier to check each digit. if first 2 digits are 24,25,26,27,28,29,30,31 then it's definately in (assuming that you can't have a number in the table that is after the current date). and then you can check the third digit. if the first three are 235,236,237,238,239 then it's within the date, and then you can check 2341,2342,2345,2346,2347,2348,2349, and then finally 23403,23404,23405,23406,23407,23408,23409. So you checked a range of 8000 numbers or so with only 17 checks.
Hope this helps.
