mysql command that deletes a row based on the present time value

[BrainPill]

 

I want to have a mysql command that deletes a row based on the present time value.

For example the column is like: first_time = 1550566437 

(the time in the table.column is based on time() in php.)

I would like to delete the rows that are older than one hour from the present time.

I want to use mysql to delete it.

My idea is that you can use now() from mysql and determine the difference in seconds (3600)

Is that possible and how is it done?

 

[BrainPill]

I also have a changed_on column with a timestamp value, is that also possible to use for the problem above?

[Funce]

An SQL command given that you already have the unix time stored in another variable would be quite simple.

DELETE FROM table WHERE time < ?

As long as you've inserted these records with their related timestamp, this should remove everything that has a timestamp less than the value you set.

The value can be calculated as

<?php
$criteria = time() - 3600; //3600 seconds is 1 hour
?>

 

[BrainPill]

I solved this meanwhile. I used the timestamp column to make the query .

I used this query :

     DELETE FROM `tab` WHERE `timestamp_col` < (NOW()– INTERVAL 10 MINUTE);

 

I works only in the mysql command line interpreter.

But I cant get it working in a batch script.

Also in phpmyadmin it does not work. The error is in the minus sign. 

when running the batch script it shows a u with a caret like this û , and phpmyadmin gives error messages.

Anyone any idea how to solve this?

Edited February 21, 2019 by BrainPill

[BrainPill]

  I made a type mismatch in PHPmyadmin. The ; should not be included inside the double quotes.

But still I can't understand the change of :  - into a : û in the batch script.

What causes this error?

[BrainPill]

I also solved it for batch files, by removing the parentheses 

[justsomeguy]

The now function returns a date, if that is an integer column then you should use the unix_timestamp function and just subtract the number of seconds.