CODEWITHSUNDEEP
mysqlwhere-clauseunix-timestamp
newbtophp
newbtophp

Reputation: 175

How to check if equals or is within X days in MySQL query?

I have a column called submit_timestamp which holds a UNIX_TIMESTAMP(), what I want to do now is do a SELECT query and within the WHERE clause ensure the submit_timestamp equals or is within X number of days.

(Demonstration purposes only):

SELECT id
FROM   submissions
WHERE  submit_timestamp = 'EQUALS OR IS WITHIN X NUMBER OF DAYS FROM submit_timestamp'
       AND id = 2

All help appreciated.

Thanks.

Upvotes: 0

Views: 402

Answers (2)

Johan
Johan

Reputation: 76567

SELECT id 
FROM submissions 
WHERE FROM_UNIXTIME(submit_timestamp) 
  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 2 DAY)
  AND id = 2;

A BETWEEN B AND C does A >= B AND A <= C.
It just communicates the intent better :-)

See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

EDIT Or even better:

SELECT id 
FROM submissions 
WHERE submit_timestamp BETWEEN UNIXTIMESTAMP() 
  AND unixtimestamp(DATE_ADD(NOW(),INTERVAL 2 DAY))
  AND id = 2;

As Dan correctly explains, this allows MySQL to use an index on submit_timestamp, which the above code does not.

Note that UNIXTIMESTAMP() with no arguments returns UNIXTIMESTAMP(NOW())
See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

Compute the two times you want to compare to in your application before you construct the query. All programming languages will have a function to give you the current timestamp (i.e. time() in PHP). To get "X days from now", add 60*60*24*X seconds to the timestamp.

SELECT id
FROM   submissions
WHERE  submit_timestamp >= $THE_CURRENT_TIMESTAMP
       AND submit_timestamp <= ($THE_CURRENT_TIMESTAMP + 60*60*24*X)
       AND id = 2

Now you're just comparing integers. Unlike Johan's solution, MySQL will be able to use an index on the column for the comparisons.

Upvotes: 2

Related Questions