Felipe S. Bueno Felipe S. Bueno - 1 month ago 6
MySQL Question

Tolerance of 10 mins after and before registered time field

I know the Title seems really confusing, but it's the closest I can get to what I really need to do...

Let me try to explain, I have a

column
in my
mySQL
table where have fields of hours (string), date of registration and access code.

To redirect to the required page, he needs to fill the
input
of access code, when he fill and submit the
form
, a entire
select
is made:

$sql = 'SELECT
agendamentos.data_agendada,
agendamentos.codigo_acesso,
horarios.horario
FROM
agendamentos
INNER JOIN horarios ON agendamentos.horario_id = horarios.id
WHERE
agendamentos.aluno_codigopessoa = '.$usrs->codigoPessoa.'
AND agendamentos.codigo_acesso = '.$_POST['acesso_chat'];

$statement = $db->prepare($sql);
$statement->execute();
$result = $statement->fetchAll();


This returns me if the access code exists, the hour that he chooses and the date.

I need to make a comparison with the hour of the result with a new
DateTime()
object, but, I can't let him go through if the hour of the result isn't in a period of 10 minutes before and 10 minutes after the one he selected.

I tried a few codes, and I really can add minutes to the object of
DateTime
, which is what I did, BUT, how do I even do one
if
statement to see if the hour he selected is in the period of 10 minutes before and 10 minutes after the current time?

Answer

You can do it either via PHP or MySQL:

PHP version:

//given your result is something like this:
$result = array(
    'codigo_acesso' => 'ABCDE',
    'data_agendada' => '2016-10-26',
    'horario'       => '14:00:00'
);


$timestamp = strtotime($result['data_agendada'] . ' ' . $result['horario']);
$now       = mktime();
$diff      = $now - $timestamp;
$tolerance = 10 * 60; //10 minutes

if (abs($diff) <= $tolerance) {
    //grant access
} else {
    //deny access
}

and MySQL version:

$tolerance = 10 * 60; //10 minutes

$sql = "SELECT
            agendamentos.data_agendada,
            agendamentos.codigo_acesso,
            horarios.horario
        FROM
            agendamentos
        INNER JOIN horarios ON agendamentos.horario_id = horarios.id
        WHERE
            agendamentos.aluno_codigopessoa = :codigo_pessoa
            AND agendamentos.codigo_acesso = :acesso_chat
            AND ABS(TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(CONCAT(agendamentos.data_agendada, ' ', horarios.horario), '%Y-%m-%d %H:%i:%s'))) <= " . $tolerance; //

$statement = $db->prepare($sql);
$statement->execute(array(
    ':codigo_pessoa' => $usrs->codigoPessoa,
    ':acesso_chat'   => $_POST['acesso_chat']
));
$result = $statement->fetchAll();

if (!empty($result)) {
    //grant access
} else {
    //deny access
}

Prefer MySQL version.