faqts : Computers : Programming : Languages : PHP : Database Backed Sites : MySQL

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

21 of 32 people (66%) answered Yes
Recently 7 of 10 people (70%) answered Yes


How do I select the entries which where inserted during the last 28 days from a table which has a datetime field?

Apr 16th, 2001 00:36
Ilya Poropudas, Matt Gregory, Oliver Kurlvink,

//When you are working with dates in mySQL, you are really working with
//strings.  For this reason you can use a greaterthan operator on any
//string field in PHP and it should work, provided you know the order
//of importance for the ASCII table identity for each character.  
("A > a, B > b, D > C, 1 > A" etc..)
//So, your answer is:
mysql_query("SELECT * FROM MyTable WHERE DateField > \"2000-01-20\"");
//Your result will be every record in the table who's DateTime value is
//greater than the day you specify in the query, if you do not specify
//a time, then the day you use in the above query will be listed as
//well, with the exception of any entries entered on the stroke of
It seems to me that it would be better to work with mySQl's date 
functions. For example selecting the last 28 days could be done like 
SELECT * FROM MyTable WHERE TO_DAYS(NOW()) - TO_DAYS(DateField) <= 28