How can I remove rows where their timestamp older than now?

Basically, I fill a row with a timestamp => '1558180800' for exemple. I want to remove all rows which their timestamp is greater than now, current timestamp.

I tried several things but didn't work :/

One example of the query I've tried:

DELETE FROM roompasswords WHERE timestamp > NOW()

Solution

Based on your example given, timestamp value of '1558180800' is UNIX_TIMESTAMP format. NOW() returns 'YYYY-MM-DD hh:mm:ss'. So if to directly translate your query:

DELETE FROM roompasswords WHERE timestamp > NOW();
is equivalent to
DELETE FROM roompasswords WHERE timestamp > '2019-05-14 09:18:06';

So it won't work given that it can't find that particular field value. Therefore, you need to change NOW() into UNIX_TIMESTAMP. Below are example you can try:

1) DELETE FROM roompasswords WHERE timestamp > UNIX_TIMESTAMP(NOW());

Note that NOW() returns current date + time so if you want to specify a time as well you can do the following instead:

2) DELETE FROM roompasswords WHERE timestamp > UNIX_TIMESTAMP('2019-05-14 00:00:00');

OR you can fetch the UNIX_TIMESTAMP value first then use that for your DELETE query:

  SELECT UNIX_TIMESTAMP('2019-05-14 00:00:00'); -- will return '1557763200'
  -- then
  DELETE FROM roompasswords WHERE timestamp > '1557763200';

Lastly, this query will work as well:

 3) DELETE FROM roompasswords WHERE FROM_UNIXTIME(TIMESTAMP) > '2019-05-14 00:00:00';
    or
    DELETE FROM roompasswords WHERE FROM_UNIXTIME(TIMESTAMP) > NOW();