MySQL compare unix timestamps

Question

I'm trying to get all records which have a date_created within 2 hours ago. It's a unix timestamp which is created from the php function time(). Here is my current query:

SELECT id from gsapi_synsets where name = "Beyonce" and date_created BETWEEN  UNIX_TIMESTAMP(date_created) and UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 2 hour))    

Doesn't seem to be working though.

Answer 1

If none of the date_created values will be in the future, you can use this:

SELECT id
FROM gsapi_synsets
WHERE name = 'Beyonce'
  AND date_created > UNIX_TIMESTAMP(NOW() - INTERVAL 2 HOUR);

If date_created values can be in the future, use this, which cuts off at the current

SELECT id
FROM gsapi_synsets
WHERE name = 'Beyonce'
  AND date_created BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 2 HOUR) AND UNIX_TIMESTAMP()

Note that calling UNIX_TIMESTAMP without an argument returns the timestamp for "right now".

Answer 2

You're trying to get all row's between that row's datestamp and 2 hours from now, which won't work. Use this instead:

SELECT id from gsapi_synsets where name = "Beyonce" and date_created BETWEEN  UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -2 hour)) and  UNIX_TIMESTAMP(NOW())