How to select a period in mysql using the date field as a unix timestamp?

Question

Hello friends, I have a table with a field where I store the date in unix timestamp, eg 1286515961.

Need, select the records that are in the month, eg 'October 2010'.

I would like to select records directly in mysql query, for speed, but using unix timestamp.

Can anyone help me? Thanks already

Answer 1

The easiest way is to use FROM_UNIXTIME:

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

Answer 2

Alec's answer is good

this would be:

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

but its actually faster to do it the other way round. convert your boundries to unix timestamp and then do the comparision. so the conversion needs not to be done for every row in the table but only for the boundries, then integers can be compared internally which is a lot faster than the translation to datetime objects... like so...

SELECT * FROM table 
WHERE date >= UNIX_TIMESTAMP('2010-10-01') 
AND date <  UNIX_TIMESTAMP('2010-11-01')

(untested)