CODEWITHSUNDEEP
phpmysqltimestamp
Dan Stern
Dan Stern

Reputation: 2167

selecting a mysql timestamp range

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

Upvotes: 3

Views: 7674

Answers (2)

Johan
Johan

Reputation: 76753

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.

Upvotes: 8

Michael Berkowski
Michael Berkowski

Reputation: 270767

If your column is a TIMESTAMP type, then it uses MySQL's default date format, NOT a Unix timestamp. It would also be wise to call DATE(fecha) so that only the date parts are compared, ignoring hours,minutes,seconds.

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

Upvotes: 0

Related Questions