Date search Sql Query

im storing the timestamp in mysql database in (INT) column, And i want to search the rows with between the dates. An开发者_如何学运维yone would please help what should be the Sql query to find the rows between two dates?

dates are entered like

FROM DATE = 15-10-2011    

END DATE = 01-11-2011

It depends on what algorithm you use to convert the date strings to int values.

If the algoritm is mototonic, for example: If a day (say 15-10-2011) is converted to n (say 5037), then the next day (16-10-2011) is always converted to n+1 (so 5038 in this example.)

then you could just use:

WHERE IntField BETWEEN MySpecialConvertDateToIntFunction('15-10-2011') 
                   AND MySpecialConvertDateToIntFunction('01-11-2011') 

If your field stores different timsetamps as different integers (and the conversion is monotonic), you could change the above code slightly to:

WHERE IntField >= MySpecial...Function('15-10-2011') 
  AND IntField < MySpecial...Function('02-11-2011')    --- notice the date+1  

But it's usually better to use a field of the MySQL DATE type for storing dates. Unless you want to store dates before 1000 or after 9999 off course.

If you want to store timestamps, there's also a TIMESTAMP type. Read the
MySQL docs: DATETIME, DATE, and TIMESTAMP Types

You can use The BETWEEN operator, which selects a range of data between two values. The values can be numbers, text, or dates.

You can see there:
http://w3schools.com/sql/sql_between.asp

I would ask you to set data type as timestamp/datestamp & then

//php code
$date1=date ('Y-m-d' , strtotime ( "15-10-2011") );
$date2=date ('Y-m-d' , strtotime ( "01-11-2011") );
//sql code
SELECT * FROM tbl_mytbl WHERE DATE(attr_date) <'$date2' AND DATE(attr_date) >'$date1'

Can you use the mysql FROM_UNIXTIME function dev.mysql.com - function from_unixtime

SELECT * 
FROM 'table' 
WHERE FROM_UNIXTIME(intTimestamp) 
BETWEEN 
date ('Y-m-d' , strtotime ( '15-10-2011') ) 
AND ('Y-m-d' , strtotime ( '01-11-2011'));

I had made a mistake with the date input but have fixed.