Recover parameter to filter in SQL: date and time every minute

Question

I have a table with a column called values and it has the following columns: ID, VALUE and DATE.

I have a while in PHP that counts 24 hours, so I need to recover the records every 30 minutes.

I need the query parameter 30 in 30 minutes.

Previous code:

while($cont<=24){
    $conta = $cont -1;
    $conta;
    $hora[$cont]  = date('Y-m-d H:i:s', strtotime("+60 min",strtotime($hora[$conta])));
    
    $consulta = $pdo->query("SELECT * FROM valores where data_hora < '$hora[$cont]' order by id desc limit 1  ");
    $resultado = $consulta->fetchAll();



    foreach($resultado as $row)
    {
      $nome_equipamento = $row['nome'];
      $valor_equipamento = $row['valor'];

      $data_alarme = $row['data_hora'];

      $data_a = date("H", strtotime($data_alarme));

      if($data_a != $conta){$valor_equipamento = 0;}

      $data_alarme = date("d/m/Y - H:i:s", strtotime($data_alarme));

       
      $cont++;  


    }
  }

To return the parameter to query in SQL, desired example:

2016-08-24 00:00:00

2016-08-24 00:30:00

2016-08-24 01:00:00

Answer 1

I think the ideal thing, since you want to consult every 30 minutes, is to adjust your counter to minutes. For example: 24hrs are 1440min, and then would decrease minutes instead of hours.

In PHP you can generate a date/time with the date() function. One observation is that this function converts seconds to hours and not minutes to hours, but to solve just place within the function the minutes multiplied by 60. Ex.:echo date("h:m:s",(150*60));

Result >> 02:30:00

I hope I’ve helped.

Answer 2

It gets simpler with a loop for time and time for minutes.

I separated a part of the code into a function to facilitate the understanding of the code:

for($hora= 0; $hora < 24; $hora++){
    $hoje = new DateTime();
    for($minuto = 0; $minuto <= 30; $minuto += 30){
        $data_hora = $hoje->format('Y-m-d') . ' '  // dia mes e ano de hoje
            . str_pad($hora, 2,'0', STR_PAD_LEFT)   // hora do primeiro loop (formatado com zero à esquerda)
            . ':' . str_pad($minuto, 2,'0', STR_PAD_LEFT) . ':00'; // minutos do segundo loop (formatado com zero à esquerda)

        // nota: os segundos estão fixos em 00 (linha acima)
    
        processa_query($data_hora ); // chama a função que irá processar o sql
    }
}

function processa_query($data_hora){
   
    
    $sql = "SELECT * FROM valores where data_hora < '$data_hora' order by id desc limit 1  ";
    $consulta = $pdo->query($sql);
    $resultado = $consulta->fetchAll();



    foreach ($resultado as $row) {
        $nome_equipamento = $row['nome'];
        $valor_equipamento = $row['valor'];

        $data_alarme = $row['data_hora'];

        $data_a = date("H", strtotime($data_alarme));

        if ($data_a != $conta) {
            $valor_equipamento = 0;
        }

        $data_alarme = date("d/m/Y - H:i:s", strtotime($data_alarme));
    }
}

The function processa_query will receive as parameter the current date and the variation every 30 minutes. Example of the parameters passed on the date 08/24/2016:

2016-08-24 00:00:00

2016-08-24 00:30:00

2016-08-24 01:00:00

2016-08-24 01:30:00

2016-08-24 02:00:00

2016-08-24 02:30:00

2016-08-24 03:00:00

2016-08-24 03:30:00

2016-08-24 04:00:00

2016-08-24 04:30:00

2016-08-24 05:00:00

2016-08-24 05:30:00

2016-08-24 06:00:00

2016-08-24 06:30:00

2016-08-24 07:00:00

2016-08-24 07:30:00

2016-08-24 08:00:00

2016-08-24 08:30:00

2016-08-24 09:00:00

2016-08-24 09:30:00

2016-08-24 10:00:00

2016-08-24 10:30:00

2016-08-24 11:00:00

2016-08-24 11:30:00

2016-08-24 12:00:00

2016-08-24 12:30:00

2016-08-24 13:00:00

2016-08-24 13:30:00

2016-08-24 14:00:00

2016-08-24 14:30:00

2016-08-24 15:00:00

2016-08-24 15:30:00

2016-08-24 16:00:00

2016-08-24 16:30:00

2016-08-24 17:00:00

2016-08-24 17:30:00

2016-08-24 18:00:00

2016-08-24 18:30:00

2016-08-24 19:00:00

2016-08-24 19:30:00

2016-08-24 20:00:00

2016-08-24 20:30:00

2016-08-24 21:00:00

2016-08-24 21:30:00

2016-08-24 22:00:00

2016-08-24 22:30:00

2016-08-24 23:00:00

2016-08-24 23:30:00

In case I have misunderstood your question is just post a comment below that I update.