Jump to content

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


BrainPill
 Share

Recommended Posts

 

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?

 

Link to comment
Share on other sites

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
?>

 

Link to comment
Share on other sites

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 by BrainPill
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...