0

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.

newbtophp
  • 175
  • 2
  • 10

2 Answers2

2

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.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
1
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

Johan
  • 74,508
  • 24
  • 191
  • 319
  • Too my knowledge NOW() returns the current time in the default MySQL format, so just curious whether you've considered that submit_timestamp is a unix timestamp (without any format)? – newbtophp May 14 '11 at 14:50
  • 1
    @newbtophp: presumably not, but +1 to his answer nonetheless. adjusting the query to your needs after reading mysql's datetime functions is certainly in your skillset. – Denis de Bernardy May 14 '11 at 15:10