Retrieve parameter for SQL filter: date and time every minute [closed]

Question

Retrieve parameter for SQL filter: date and time every minute [closed]

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

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

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

I need the parameter to query 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++;  


    }
  }

How to return the parameter for query in SQL, desired example:

2016-08-24 00:00:00

2016-08-24 00:30:00

2016-08-24 01:00:00

php mysql

asked by anonymous 24.08.2016 / 15:46

2 answers

2

I think the ideal, since you want to consult every 30 minutes, is to set your meter for minutes. For example: 24 hours is 1440min, and then it would decrease the minutes instead of the hours.

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

Result > > 02:30:00

I hope I have helped.

24.08.2016 / 16:29

What is the reason for the error "The operator / is undefined for the argument" in this code? How to list files in a directory?

score 2 · Accepted Answer

It is simpler with loop for time and another for minutes.

I've separated a part of the code into a function to make it easier to understand 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 processa_query function will receive the current date and the variation every 30 minutes as a parameter. Example of parameters passed on 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

If I misunderstand your question, just post a comment below that I update.