Time Comparison Problem with mysql

Asked

Viewed 898 times

2

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

When you arrive at a delivery period like Das 22:00 às 02:00 the delivery time is 55min the SELECT simple with the BETWEEN is not solving the problem.

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

Let’s say it’s now 11:00 23:00 ENTRE 22:00 E 02:00, only it doesn’t add up because 02:00 It’s 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 only:

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 instruction (I believe I have adapted correctly in Mysql, I have no 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 got back: tempo: 00:25:00

So far, everything perfect... masss... how much I traded "CURTIME()" for "11:30 am" the return was:

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

I imagine, because I used the OR he took both forms... but using the AND in place of the OR does not return either. :(

php mysql

  • Post the code you are using to give SELECT in both 40min and 55min time

  • 1

    Can you use the date too? or only' has even the hours/minutes?

  • The type used for these two fields is DATETIME?

  • I edited up there with the SQL I used, the fields hora_start and hora_end, are of the type TIME and I do not use the date in this case, because this will be valid for any day of the week. but I accept suggestions.

  • The easiest solution would be to separate this entry "22:00 until 02:00" in 2 entries: 1 - "22:00 until 23:59:59" 2- "00:00 until 02:00", so avoid the problem of the BETWEEN dates, or you will have to implect this logic in the SQL input

  • I think this will end up being the best shape even John Fox. The only annoying thing is going to be having to treat these duplicated entries so the user can remove them or change them. That was the main reason I persisted with this more 'dry' form. But you commented on implementing in the SQL statement, could give me a help with this, I understand a little but I am not as deep with SQL, for lack of time and specific needs I ended up learning only what was needed =/

Show 1 more comment

2 answers

0

It seems to me that the problem lies in the modeling of your table. Theoretically, it should be 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 >= ?;

  • But my table is exactly like this Rodrigo, the only difference is that in his example, apparently the time fields are of type INT, and in mine are TIME because there may be minutes together the hours... in its form would fall into the same problem because this data who "register" is a user. that is, a restaurant, and I think it would not be very usual, he have to register taking care of whether past midnight or not, and nor does the system block it.

  • I understand. In this case I believe that there would not be much difference because, being TIME or being INT the query is the same for both. Only in WHERE would it become something like WHERE CAST('05:00:00' AS time).

  • From what I understand, the problem of @Stedile93 is that it cannot structure the table this way and will have to keep "22:00 - 02:00" which is the user input

  • That’s right @Joaoraposo (now that I’ve seen how to mention someone in the comment). The user will inform me an initial time and a final time for that particular delivery time, and that final date may end up being the next day.

  • What if you have two (or more) delivery times for that period? Anyway, if the second value is less than the first (which means past midnight), I would put the condition of WHERE twice: one for the period from 0h to 2h, and another for the period from 22h to 24h.

0


Your table structure is making the work difficult, the most correct and you work with whole dates and for calculations the best is 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 hora_dia_start and hora_dia_end fields in seconds.

  • Hello @user5418 I liked this way of solving the problem, I just had some good ideas about this, because with the SQL statement I think I was not very happy, the idea of Joao Raposo was great tbm, but I think I could not pass the same way to the syntax of Mysql. But this way, I think it’ll be fine. Thank you!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.