I have a Atendimento table in my DB. Inside it I have two fields that are: HrIni which refers to the start time and another HrFim which refers to the end time.
I need for each record of these two fields to be stored in a new field tempo the value YES for calls that lasted
You can use the TIMEDIFF() function.
Example:
SELECT TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002')
Source: link
I do not know what language you are using, but in PHP I would do so:
<?php
$con=mysqli_connect("exemplo.com","peter","abc123","my_db");
// Verifica conexão
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT id, TIMEDIFF(HrIni,HrFim) AS diferenca FROM Atendimento");
$row = mysqli_fetch_array($result);
$sim_nao = ($row["diferenca"] > 20) ? 'SIM' : 'NAO';
mysqli_query($con,"UPDATE Atendimento SET tempo='".$sim_nao."' WHERE id='" . $row['id'] ."'");
mysqli_close($con);
?>
Rodrigo,
I think a Trigger would meet your need. I have never created one, but the idea of the trigger is as follows, after an INSERT, DELETE or UPDATE the trigger is triggered and the code belonging to the trigger is executed. Read about triggers.
Edited:
In a very thick way, the creation of your trigger would look like this:
CREATE TRIGGER nome_trigger AFTER UPDATE ON nome_tabela
FOR EACH ROW
BEGIN
#SEU CODIGO VEM AQUI, ALGO COMO:
IF NEW.hrIni IS NOT NULL AND NEW.hrFim IS NOT NULL THEN
IF TIMEDIFF(NEW.hrFim, NEW.hrIni) > 20 THEN
NEW.tempo = 'SIM'
ELSE
NEW.tempo = 'NAO'
END IF;
END IF;
END;