Using $tempo = date("d/m/Y H:i:s",time()-86400); to delete past schedules

Question

I am using this code to delete schedules with dates and times already past the current time.

<?php
while($res = mysql_fetch_array($select_agenda)){

$id = $res['id'];
$nome = $res['nome'];
$tel = $res['tel'];
$cel = $res['cel'];
$email = $res['email'];
$plano = $res['plano'];
$data = $res['data'];
$horas = $res['horas'];
$tempo = date("d/m/Y  H:i:s",time()-86400);

$sql = mysql_query("DELETE FROM agendar WHERE data < '$tempo'");
?>

Knowing that 86400 refers to the number of seconds of a day, I am using the code above, and excluding the past schedules referring to the current time, at the turn of the dates.

Following this reasoning, I changed to -1800, so that the files were deleted every 30 minutes. Getting like this:

$tempo = date("d/m/Y  H:i:s",time()-1800);
$sql = mysql_query("DELETE FROM agendar WHERE data < '$tempo'");

But this way it is excluding all records, and not the last 30 minutes referring to real time.

Scheduling is done at 30-minute intervals.

If friends can give me a help on how I should proceed to schedules being deleted every 30 minutes, I would be very grateful.

Big hug to all.

Answer 1

I don’t know if I understood the question correctly, but it is possible to delete the records with a field type "datetime" with values less than 30 minutes of the current time using the exclusion query a mysql NOW() function:

$sql = mysql_query("DELETE FROM agendar WHERE data <  NOW() - INTERVAL 30 MINUTE");

Remembering that NOW() returns the current date/time, and with the INTERVAL can add (using +) or subtract (using -) of the current time (i.e., of the value returned by NOW()).

Where I used MINUTE, it is possible to replace with: YEAR to year, MONTH to month, DAY to day, HOUR for the time, MINUTE for minute, SECOND for second.

NOTE: it is worth remembering that the mysql LIB is deprecated for versions starting with PHP 5.5 , utilize PDO or Mysqli.

I hope I’ve helped!

Answer 2

To subtract 30 minutes from your initial PHP time instead of $tempo = date("d/m/Y H:i:s",time()-1800); you can do:

$tempoInicial = date("d/m/Y  H:i:s",time());
$menosTrintaMinutos = date("Y-m-d H:i:s", strtotime("-30 minutes", strtotime($tempoInicial )));

//echo 'tempo inicial: '.$tempoInicial.'<br/>';
//echo 'tempo -30m: '.$menosTrintaMinutos;

$sql = mysql_query("DELETE FROM agendar WHERE data < '$menosTrintaMinutos '");

Running $sql you delete all records from the table agendar where there are more than 30 minutes passed (when the column data is less than the current date minus 30 minutes).