Intervals of time between records

Question

Intervals of time between records

Navigation

#1 by (1 votes)

0

I have a table that is composed as follows:

Schedules

id int(11) pk uq nn ai
inicio datetime nn default "CURRENT_TIMESTAMP"
fin datetime null default "CURRENT_TIMESTAMP"
tipo tinyint(1) nn default 0
eliminado tinyint(1) nn default 0

The fact is that these schedules correspond to different users and I wanted to find a way to know if some of them coincide with other schedules of system users

At first, I thought that with a "BETWEEN" it would be solved:

SELECT inicio, fin FROM horarios
WHERE eliminado = 0 AND tipo = 0
BETWEEN TIMESTAMP('2018-01-10','06:30:00') AND TIMESTAMP('2018-01-10','12:30:00')

But MySQL is returning almost any date found in the table (several other days inclusive)

Is there any way to get the result I'm looking for?

Note 1 : I use PHP (rotating between v5 and v7 to check compatibility), MySQL as manager and InnoDB as engine.

Note 2 : I leave a clear example, because maybe my way of explaining is pretty bad:

Start : '2018-01-12 13:30:00'

End : '2018-01-12 19:30:00'

What is required, is the set of schedules that are in that time slot (no matter how much they share at least 1 minute). Thank you very much.

mysql sql datetime date time

asked by Faju 12.01.2018 в 17:36

source

1 answer

Obtain width of an element using Angular 4 Detect change in a CheckBox within a TableView

score 1 · Accepted Answer

You are doing the wrong query, it should be:

SELECT inicio, fin FROM horarios
WHERE eliminado = 0 
AND tipo = 0
AND ((inicio BETWEEN TIMESTAMP('2018-01-10','06:30:00') AND TIMESTAMP('2018-01-10','12:30:00'))
OR (fin BETWEEN TIMESTAMP('2018-01-10','06:30:00') AND TIMESTAMP('2018-01-10','12:30:00'))) ;