MySQL timestamp select date range

Question

Not sure really where to start with this one. Can anyone help/point me in the right direction.

I have a timestamp column in MySQL and I want to select a date range for example, all timestamps which are in Oct 2010.

Thanks.

Answer 1

SELECT * FROM table WHERE col >= '2010-10-01' AND col <= '2010-10-31'

Answer 2

Use the FROM_UNIXTIME() function to convert, then do the range checks. The function allows you to specify apply format too. Example:

WHERE FROM_UNIXTIME( time_field, "%Y%m" ) = '201010'

Answer 3

SELECT * 
  FROM yourtable
 WHERE MONTH(FROM_UNIXTIME(yourtimetimefield))=10

• MONTH() extracts the month from a date
• FROM_UNIXTIME() converts to a datetime

Alternatively, you can of course use ranges

SELECT * 
  FROM yourtable
 WHERE FROM_UNIXTIME(yourtimetimefield)>='2010-10-01'
   AND FROM_UNIXTIME(yourtimetimefield)< '2010-11-01'

Answer 4

A compact, flexible method would be:

SELECT * FROM table_name 
WHERE field_name 
BETWEEN UNIX_TIMESTAMP('2010-09-31') AND UNIX_TIMESTAMP('2010-10-10')

Answer 5

Whenever possible, avoid applying functions to a column in the where clause:

SELECT *
  FROM table_name
 WHERE timestamp >= UNIX_TIMESTAMP('2010-10-01 00:00:00') 
   AND timestamp <  UNIX_TIMESTAMP('2010-11-01 00:00:00');

Applying a function to the timestamp column (e.g., FROM_UNIXTIME(timestamp) = ...) makes indexing much harder.