Quickest way to prevent SQL returning data older than Given

Hey there

I have a pretty complex database with loads of different tables for different things, every thing of which has a timestamp (Y-M-D h:m:s format)

Is there a way I can limit my SQL query to just results from a certain timespan, for example a week ago?

If needbe I have a function to convert these timestamps into unix as

sqlToUnix($date);
//returns $unixTime

so for example

mysql_query(SELECT id FROM entries WHERE entries.date >= $formattedDateString);

Thanks!

Just a reminder the database dates are of the format: 2009-02-15 08:47:45

From stackoverflow

• Yes, like you have written you can make greater than and lower than restrictions on your output. Where is the problem?

  If your field is no timestamp/datetime field but a string, use the MySQL function STR_TO_DATE() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html)

• That's totally possible with the BETWEEN-statement (Since MySQL 4.0):

  mysql_query("SELECT id FROM entries WHERE entries.date BETWEEN 'Y-M-D h:m:s' AND 'Y-M-D h:m:s'");
  

  Also, to calculate the timestamp one week before the current date have a look at the MySQL Online Documentation.

  Caffeine : You can have mutliple boolean statements after the where clause by concatenating them with "AND". So for instance: "WHERE id=5 AND date BETWEEN '2009-02-15' AND '2009-02-09'" would check wether the id equals five and the date is within the last week.

  Manuel Ferreria : Just append the condition to the end of the current WHERE. If you don't touch the rest of the query, it should not matter.

• I'm a MSSQL user myself, but looking up online I can see a few usefull pieces of information for MySQL 5.0...

  curdate()
  date_add()

  With this you should be able to create a WHERE clause something like...

  WHERE
     <field> >= date_add(curdate(), INTERVAL -7 DAY)
  

  As mentioned elsewhere, if the field is a string, convert it to a date using STR_TO_DATE()