select records with date in the last 24 hours

You already have a lower limit on the date, but since your table can have future dates you also need an upper limit. This should work:

SELECT *
FROM my_table
WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND date <= NOW()

mysql:

SELECT * FROM my_table WHERE date >= now() - INTERVAL 24 HOUR;