Select from time to time

Asked

Viewed 854 times

2

I’m trying to ride a sql to check if there is any record between times informed but not leaving, I did some tests but unsuccessfully.

For example, a bank record containing these two times, as initial and final respectively:

Horaini: 8:00 and Horafim: 8:30

Any period beginning or ending during that reporting period above must be selected/returned in sql

Periodos que devem retornar o registro citado a cima
7:45 até 8:15
8:00 até 8:15

But periods like these should return nothing:

Periodos sem retorno
7:45 até 8:00
8:30 até 8:45

It’s a logic problem, where I can’t hit the maior, menor e igual to return what I need.

The bank is a MYSQL the fields are TIME and this select will run in a function Java, that part is not the problem, but stays here to address the possible doubts.

I hope it was clear, I’ve looked at several examples and found nothing, if you look at the detail that the times are the same beginning and end, where the same time that ends one record starts another.

mysql sql algorithm logic

  • The type of the Horaini and Horafim columns is timestamp?

  • It was not clear no.

  • Columns are Time type only

1 answer

2


You need the records where Start Time is between 8 and 8:30, OR, End Time is between 8 and 8:30

So we can have the following query:

Select * from tabela 
where 
(horaini >= '8:00' and horaini <= '8:30')
 OR
(horafim >= '8:00' and horafim <= '8:30')

ps. You didn’t put the table structure, not even the query you’re trying, so I just did an example with the data that was passed. If you need more details, edit the question by adding what is missing.

Edit:

Select * from tabela 
where 
(horaini >= '8:00' and horaini < '8:30')
 OR
(horafim > '8:00' and horafim <= '8:30')
  • 1

    The idea I found valid, but I fear that depending on the sql engine it interprets the comparator as lexicographical order. This would only affect in cases of invalid schedules, but even so I found it valid to raise this observation

  • 1

    @Jeffersonquesado, yeah, I gave as an example based on what he posted, as if the column was the type TIME. I think the difficulty was being logical, so I didn’t bother to confirm it before. If he doesn’t solve it, you’ll have to explain your problem better =]

  • Thank you Rovann, it is a logic problem even though the problem continues. If I add a record that termina as 8:00 and add another that começa as 8:00, it should not return because it is two distinct times one ended and the other one is starting, but with its select above, it is returning the record that ended :/

  • @P.Dominges just change the condition of >= for > and then will not be returned such record.

  • @P.Dominges put some data that is in your database for example terms, and what is the structure of the tables.

  • 1

    Rovann, thank you! It worked, as you said, I removed the condition = where the comparison is made between the initial and final time and left where the comparison is between two initial or final fields. In your answer would be horaini < 8:30 and horafim > 8:00 keeping the other two comparisons equal. Thank you!

Show 1 more comment

Browser other questions tagged mysql sql algorithm logic

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