CODEWITHSUNDEEP
mysqldate
somejkuser
somejkuser

Reputation: 9040

MySQL compare unix timestamps

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.

Upvotes: 6

Views: 16589

Answers (2)

aynber
aynber

Reputation: 23035

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())

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26363

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".

Upvotes: 10

Related Questions