MySQL - return only entries from the past X days

Question

I'm working with a database that has date information stored as a Unix timestamp ( int(11) ) and what I want to do is only return entries from the past X days, the past 90 days for example.

What I've come up with is:

SELECT * FROM mytable WHERE category=1 AND 
FROM_UNIXTIME( time ) > DATE_SUB(now(), INTERVAL 91 DAY)

Where 'time' is the int(11) in the db. This seems to be working fine, but just wondering what others think of this.

Answer 1

SELECT * FROM mytable WHERE category=1 AND 
time > (UNIX_TIMESTAMP() - ((60*60*24)*90))

or simply

SELECT * FROM mytable WHERE category=1 AND time > (UNIX_TIMESTAMP() - (86400*90))

this is just comparing a number (seconds in this case)

Answer 2

Just thinking aloud... wouldn't doing it the other way around cause less work for the DB?

time > UNIX_TIMESTAMP( DATE_SUB(NOW(), INTERVAL 91 DAY) )

Answer 3

This query is bound to cause you headaches down the way as MySQL needs to do the conversion of dates for every row making use of indexes impossible. Unix timestamps are numbers, so instead of converting a timestamp to another date format, convert your lookup dates to unix timestamps.

Answer 4

What is the reason for storing the timestamp as an int ? I would use the mysql DATETIME data type because you can use the many Date Time functions mysql has.

If you do not have control over the data type of this field I would convert your date to the unix timestamp int before you do your query and compare it that way.