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

Entry

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
//midnight.
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 
this: 
SELECT * FROM MyTable WHERE TO_DAYS(NOW()) - TO_DAYS(DateField) <= 28