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
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.
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)
I'm a MSSQL user myself, but looking up online I can see a few usefull pieces of information for MySQL 5.0...
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()