Diferença entre datas Array

[Alcaponepone 0]

Boa tarde

 

 estou fazendo um trabalho de TCC preciso saber a o tempo total que uma lampada ficou ligada usando a tabela de dados Mysql que criei que grava a hora que foi acionada e des-acionada.

para saber o horário que a lampada ficou ligada preciso da diferença de Datatime. Estou retornando as datas liga e desligada em array() mais não consigo calcular pois acho que array esta gravando os dados como string;

 

 

sugestões são bem aceitas,

 

 

Obrigado

 

$sql = "SELECT lamp_id, lamp_data, lamp_estado FROM lampadas_rel where lamp_estado=0 ORDER BY lamp_sec";
$result = $conn->query($sql);
    if ($result->num_rows > 0) {
        $i=0;
        while($row = $result->fetch_assoc()){
            $desli[]= Date('d/m/Y H:i:s',  strtotime($row["lamp_data"]));
         
        }
        print_r($desli);
        
        
    }
    
echo "<br>";
    
$sql = "SELECT lamp_id, lamp_data, lamp_estado FROM lampadas_rel where lamp_estado=1 ORDER BY lamp_sec";
$result = $conn->query($sql);
    if ($result->num_rows > 0) {
        $i=0;
        while($row = $result->fetch_assoc()){
            $liga[]= Date('d/m/Y H:i:s',  strtotime($row["lamp_data"]));
         
        }
        print_r($liga);
        
        
    }    
    
$dtar = $liga - $desli;

 

[HwapX 20]

lamp_id é a chave primária da tabela ou identifica lampadas distintas e lamp_sec é uma sequencia incremental, ou ele agrupa o evento de ligar e desligar, ou identifica lampadas distintas?

 

Se for possível e do seu interesse publique o esquema da tabela lampadas_rel junto de alguns dados de exemplo, pois talvez você possa conseguir essa informação diretamente do banco, seja com uma consulta que calcule o tempo entre cada registro ou pré calculando via triggers.

[HwapX 20]

Partindo do principio de que a tabela armazena somente o estado de uma única lampada você poderia recuperar a duração de cada intervalo em que a lampada ficou ligada com uma seguintes consultas.

/*Retorna a diferença em segundos, você deve tratar a formação na sua aplicação*/
SELECT
  l_ligada.lamp_id AS ligada_id,
  MIN(l_desligada.lamp_id) AS desligada_id,
  MIN(UNIX_TIMESTAMP(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP)) - UNIX_TIMESTAMP(l_ligada.lamp_data)) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id > l_ligada.lamp_id AND l_desligada.lamp_estado = 0
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_id ASC

/*Ou, retorna a diferença como TIME o valor maximo é 838:59:59 https://dev.mysql.com/doc/refman/5.7/en/time.html)*/
SELECT
  l_ligada.lamp_id AS ligada_id,
  MIN(l_desligada.lamp_id) AS desligada_id,
  SEC_TO_TIME(MIN(UNIX_TIMESTAMP(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP)) - UNIX_TIMESTAMP(l_ligada.lamp_data))) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id > l_ligada.lamp_id AND l_desligada.lamp_estado = 0
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_id ASC

/*Ou, retorna a diferença como TIME o valor maximo é 838:59:59 https://dev.mysql.com/doc/refman/5.7/en/time.html)*/
SELECT
  l_ligada.lamp_id AS ligada_id,
  MIN(l_desligada.lamp_id) AS desligada_id,
  MIN(TIMEDIFF(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP), l_ligada.lamp_data)) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id > l_ligada.lamp_id AND l_desligada.lamp_estado = 0
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_id ASC

Você pode testar as consultas acima em http://sqlfiddle.com/#!9/bfe330/5.

 

Partindo de uma consulta semelhante você poderia calcular a diferença diretamente no PHP.

SELECT
  l_ligada.lamp_id AS id_ligada,
  MIN(l_desligada.lamp_id) AS id_desligada,
  l_ligada.lamp_data AS ligada_em,
  MIN(l_desligada.lamp_data) AS desligada_em
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id > l_ligada.lamp_id AND l_desligada.lamp_estado = 0
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_id,
  l_ligada.lamp_data
ORDER BY
  l_ligada.lamp_id ASC

Dessa forma você poderia calcular a diferença com o método diff(http://php.net/manual/en/datetime.diff.php) da classe DateTime(http://php.net/manual/en/class.datetime.php) e formatá-lo posteriormente com o método format(http://php.net/manual/en/dateinterval.format.php) classe DateInterval(http://php.net/manual/en/class.dateinterval.php).

//http://php.net/manual/en/class.datetime.php
$ligada_em = new DateTime($row->ligada_em);
$desligada_em = new DateTime($row->desligada_em);

//http://php.net/manual/en/datetime.diff.php
$intervalo = $ligada_em->diff($desligada_em);

//http://php.net/manual/en/dateinterval.format.php
echo $intervalo->format('%D/%M/%Y %H:%I:%S');

Porem se você não deseja alterar a consulta, pode fazer o seguinte no PHP.

$sql = "SELECT * FROM lampadas_rel ORDER BY lamp_id ASC";

//...

$periodos = [];
$periodo = [];
while($row = $result->fetch_assoc()){
  if($row->lamp_estado) {
    $periodo['ligada_em'] = new DateTime($row->lamp_data);
  } else {
    $periodo['desligada_em'] = new DateTime($row->lamp_data);
    $periodo['intervalo'] = $periodo['ligada_em']->diff($periodo['desligada_em']);
    $periodos[] = $periodo;
    $periodo = [];
  }
}

if($periodo) {
  $periodo['desligada_em'] = new DateTime();
  $periodo['intervalo'] = $periodo['ligada_em']->diff($periodo['desligada_em']);
  $periodos[] = $periodo;
}

Dessa forma você irá ter um array com todos os períodos que lampada ficou acesa.

 

Não vou entrar em detalhes sobre como obter o tempo total, mas você pode conseguir utilizando o SUM no MySQL ou com algum "hack" utilizando dois objetos DateTime e o método add no PHP(https://stackoverflow.com/questions/11556731/how-we-can-add-two-date-intervals-in-php).

[Alcaponepone 0]

Em 02/04/2018 at 06:34, HwapX disse:

lamp_id é a chave primária da tabela ou identifica lampadas distintas e lamp_sec é uma sequencia incremental, ou ele agrupa o evento de ligar e desligar, ou identifica lampadas distintas?

 

Se for possível e do seu interesse publique o esquema da tabela lampadas_rel junto de alguns dados de exemplo, pois talvez você possa conseguir essa informação diretamente do banco, seja com uma consulta que calcule o tempo entre cada registro ou pré calculando via triggers.

Correto lamp_id identifica a lampada e lamp_sec é a sequencia do acionamento.  Estou usando lamp_sec não esta distinguido as lampadas a sequencia é por acionamento independente qual seja a lampada.

 

Minha tabela esta desta maneira.

lamp_sec guarda a seguencia de acionamento.

lamp_id que identifica qual a lampada acionada. 

lamp_data - marca horário que a lampada foi ligada ou desligada

lamp_estado - guarda o estado que ela estava ligada ou desligada

[HwapX 20]

Nesse caso basta ajustar as consultas e adicionar o lamp_Id ao group by.

SELECT
  l_ligada.lamp_id,
  l_ligada.lamp_sec AS ligada_sec,
  MIN(l_desligada.lamp_sec) AS desligada_sec,
  MIN(TIMEDIFF(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP), l_ligada.lamp_data)) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id = l_ligada.lamp_id AND 
  l_desligada.lamp_estado = 0 AND
  l_desligada.lamp_sec > l_ligada.lamp_sec
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_sec,
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_sec ASC
  
/*ou intervalo em segundos*/

SELECT
  l_ligada.lamp_id,
  l_ligada.lamp_sec AS ligada_sec,
  MIN(l_desligada.lamp_sec) AS desligada_sec,
  MIN(UNIX_TIMESTAMP(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP)) - UNIX_TIMESTAMP(l_ligada.lamp_data)) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id = l_ligada.lamp_id AND 
  l_desligada.lamp_estado = 0 AND
  l_desligada.lamp_sec > l_ligada.lamp_sec
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_sec,
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_sec ASC

/*Ou */

SELECT
  l_ligada.lamp_id,
  l_ligada.lamp_sec AS ligada_sec,
  MIN(l_desligada.lamp_sec) AS desligada_sec,
  SEC_TO_TIME(MIN(UNIX_TIMESTAMP(IFNULL(l_desligada.lamp_data, CURRENT_TIMESTAMP)) - UNIX_TIMESTAMP(l_ligada.lamp_data))) as tempo_ligada
FROM
  `lampadas_rel` AS l_ligada
LEFT JOIN
  lampadas_rel AS l_desligada
ON
  l_desligada.lamp_id = l_ligada.lamp_id AND 
  l_desligada.lamp_estado = 0 AND
  l_desligada.lamp_sec > l_ligada.lamp_sec
WHERE
  l_ligada.lamp_estado = 1
GROUP BY
  l_ligada.lamp_sec,
  l_ligada.lamp_id
ORDER BY
  l_ligada.lamp_sec ASC

A consulta acima pode ser testada em https://www.db-fiddle.com/f/8QeFNzPeGrN5ybhzQ5uPd1/0.

 

No PHP você teria que agrupar por lampada, fora isso o que disse anteriormente continua se aplicando.