Treat ranges between two Time Mysql fields

Question

I have a table Atendimento in my comic book. Inside it I have two fields that are: HrIni which refers to the initial and other time HrFim which refers to the final time.

I need each record of these two fields to be stored in a new field tempo the value YES for appointments that lasted more than 20 min.; and the value NAO for appointments that lasted less than 20 min. In case I would have to make the calculation of the interval enter Hrini and Hrfim (Format - 00:00:00).

Can anyone help me? In case I have not been clear I ask you to inform me.

Answer 1

Rodrigo,

I think a Trigger would meet your need. I never created one, but Trigger’s idea is as follows, after an INSERT, DELETE or UPDATE to Trigger is fired and the code belonging to Trigger is executed. Take a read on triggers.

Edited:

In a very thick way, the creation of your Rigger 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;

Answer 2

You can use the function TIMEDIFF().

Example:

SELECT TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002')

Source: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

I don’t 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);
?>