selecting a mysql timestamp range

Question

Im trying to select a timestamp range from a mysql database and im having problems, no idea why.

I have the table 'pedido' and the field 'fecha' which is set as timestamp. I have lots of entries so that should not be the problem.

However when y select with this query:

 SELECT * FROM pedido WHERE fecha BETWEEN UNIX_TIMESTAMP('2011-10-10') AND UNIX_TIMESTAMP('2011-11-10') ORDER BY fecha DESC

I get no response.

Thanls for the help

Answer 1

MySQL does not store date/time data as integer.
UNIX_TIMESTAP() returns an integer listing the seconds since 1-1-1970.
You are comparing dates against an integer. This will never work.

Rewrite the query to:

SELECT * 
FROM pedido 
WHERE fecha BETWEEN '2011-10-10 00:00:00' AND '2011-11-10 23:59:59' 
ORDER BY fecha DESC 

Note that php does use unix_timestamps So if you want to compare in php, you need to either convert to unix_timestamp in your SQL-statement or convert in your php code.