Selecting withing a range of timestamps

[RyanB88]

I am trying to select records within a range of timestamps and I cannot get my query to work and was hoping someone could enlighten me as to why

SELECT * FROM database WHERE timestamp BETWEEN '2012-09-27 03:58:54' AND '2012-09-26 22:58:54'

Thanks in advance for your help.

[Old Pedant]

It looks right, except that since timestamp is a keyword in MySQL you need to enclose it in backticks. Oh...and database is also a keyword, so same problem.

I assume that your column named timestamp is indeed of data type timestamp?

SELECT * FROM `database` 
WHERE `timestamp` BETWEEN '2012-09-27 03:58:54' AND '2012-09-26 22:58:54'

*If* your timestamp column is actually a UNIX (Linux) timestamp--and therefore just an INT data type in your schema--then you have to use a function to convert it...or convert the two BETWEEN times to UNIX timestamps (which will be much more efficient if that field is indexed).

In the future, make life simpler for yourself: Don't use MySQL keywords for column names.

[RyanB88]

I already though of that but renaming it to time_stamp did not solve the problem, neither did the `` for that matter.

The field is a timestamp type.

[Old Pedant]

Silly me! I didn't notice before. Your two BETWEEN values are *BACKWARDS*!!!

The lower one MUST come first! (Which means earlier one in the case of timestamps.)

http://dev.mysql.com/doc/refman/5.5/...erator_between