Quickest way to prevent SQL returning data older than Given

Question

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

Answer 1

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)

Answer 2

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.

Answer 3

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()