A
A
Alexander2018-09-23 10:58:58
SQL
Alexander, 2018-09-23 10:58:58

Is it correct to use multiple BETWEEN(x10) in my cases?

There is an event table - event_dates , fields - start_date, end_date . You need to get all the events that are busy on a specific date.

array_dates = [
["2018-09-10 10:00:00", "2018-09-10 12:00:00"], # 0 - начало события, 1 - конец события
["2018-09-10 14:00:00", "2018-09-10 16:00:00"],
["2018-09-10 18:00:00", "2018-09-10 20:00:00"],
["2018-09-10 22:00:00", "2018-09-11 00:00:00"],
]

SELECT * FROM event_dates
WHERE (start_date BETWEEN array_dates[0][0] AND array_dates[0][1])
OR (end_date BETWEEN array_dates[0][0] AND array_dates[0][1])

OR (start_date BETWEEN array_dates[1][0] AND array_dates[1][1])
OR (end_date BETWEEN array_dates[1][0] AND array_dates[1][1])

OR (start_date BETWEEN array_dates[2][0] AND array_dates[2][1])
OR (end_date BETWEEN array_dates[2][0] AND array_dates[2][1])

OR (start_date BETWEEN array_dates[3][0] AND array_dates[3][1])
OR (end_date BETWEEN array_dates[3][0] AND array_dates[3][1])

Is there a more optimized way?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Andrey, 2018-09-23
@VladimirAndreev

Her so.
Select * from item t where 1=1
or (ts>'2018-01-01 10:00:00' and te<'2018-01-01 12:00:00)
or (ts>'2018-01-01 14:00:00' and te<'2018-01-01 16:00:00)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question