Problem with time comparison with mysql

Navigation
2

I have a problem: I have a table of delivery times with times for each period. Ex .: From 14:00 às 22:30 the delivery time is 40min . I can get this with SELECT using BETWEEN .

When arriving in a delivery period as Das 22:00 às 02:00 delivery time is 55min SELECT simple with BETWEEN is not solving the problem. 

hora_inicio: 22:00
hora_fim: 02:00
tempo: 00:55

Let's suppose it's now 11:00 PM so it gets 23:00 ENTRE 22:00 E 02:00 , only that does not hit because 02:00 is already the next day, right?

The SQL statement used in the 'easy part' was: 

SELECT tempo FROM tempo_entrega WHERE id_restaurante = 12 AND CURTIME() BETWEEN hora_inicio AND hora_fim

Following the line of reasoning of @JoaoRaposo look: 

id_restaurante:12
hora_inicio: 22:00:00
hora_fim: 02:00:00
tempo: 00:40:00

id_restaurante:12
hora_inicio: 02:30:00
hora_fim: 16:30:00
tempo: 00:25:00

I applied the following statement (I believe I have adapted correctly in Mysql, I do not have knowledge of IF ELSE within SQL: 

SELECT tempo FROM tempo_entrega WHERE id_restaurante = 12 AND CURTIME() BETWEEN hora_inicio AND hora_fim OR  id_restaurante = 12 AND CURTIME() BETWEEN hora_inicio AND '23:59:59' OR id_restaurante = 12 AND CURTIME() BETWEEN '00:00:00' AND hora_fim

And I had a return: tempo: 00:25:00

So far, everything perfect ... masss ... how much I changed the "CURTIME ()" by "23:30:00" the return was: 

tempo: 00:40:00
tempo: 00:25:00

I imagine that because I have used the OR, it has taken both ways ... but using the AND instead of the OR does not return either one or the other. :

    
asked by anonymous 17.02.2014 / 16:53

2 answers

0

Your table structure is making it harder to work, more correct and you work with whole dates and for calculations it is best to convert in seconds. see an example: 

<?php
$agora = date("d-m-Y H:i:s");//EX: 17/02/2014 19:00:24
$agora_em_segundos = strtotime($agora);//EX: 1392660273
// OU
$agora = time();//EX: 1392660273
//Entre dias
$anterior = strtotime('2014-02-16 23:30:00');//EX: 1392589800
$atual = strtotime('2014-02-17 00:25:00');//EX: 1392593100

$calculo = $atual - $anterior;//EX: 3300
$duracao = segundosParaMinutos($calculo); // 55 min

function segundosParaMinutos($seg){
    return (int)$seg / 60;
}

From this example you may want to change your tables and then save in the fields start_time_and_day_time in seconds.

    
17.02.2014 / 19:37
0

It seems to me that the problem lies in modeling your table. Theoretically, it should look something like this: 

hora_dia_inicio hora_dia_fim tempo_entrega
              0             2           55
              2            22           40
             22            24           55

And then the query would be: 

SELECT tempo_entrega
FROM tempo_entrega
WHERE hora BETWEEN hora_dia_inicio < ? AND hora_dia_fim >= ?;
    
17.02.2014 / 17:08
How to use a local directory as a dependency? How to inherit from a ServerModule in DataSnap with Delphi XE3