How to write a "tricky" MySQL query?

Question

MySQL

Hello! Please tell me how to do it better, and how to do it in general.
There is a table in the MySQL database that has 2 TIMESTAMP columns: start, end.
start - date + time of the beginning of a certain process, end - end.
You need to select all records whose time interval [start,end] covers, say, 16:00, i.e. During this time, the clock at least once, but showed 16:00.
For example:
01-01-2012 12:00; 01-01-2012 18:00 is fine, but
01-01-2012 17:00; 01-01-2012 18:00 - not suitable, but
01-01-2012 23:00; 01/02/2012 17:00 - suitable.
SELECT * FROM `table` WHERE ?????
MySQL is not a guru. I will be very grateful!
PS At first, I accidentally placed it not in questions, but in posts. Who managed to see - everyone "sorry" :-)

Answer 1

So, I correct myself.
In order for our interval not to fall into 16 hours, it must be entirely located between the two nearest 16-hour marks on the time line. They can be obtained artificially: the first mark is the latest date-time, when it was 4 p.m., but no later than start, and the second mark is exactly one day later. Let's conditionally call these marks aand b. Since it startalways hits [a, b], the whole problem boils down to checking if it hits endthe interval [a, b]. With , there will be no 16-hour mark end < bin the interval . [start, end]Now let's return to the original problem. We reverse the condition: at , at least one mark at 16 o'clock will fall end >= binto the interval . Now let's try to get . To obtain[start, end]
And in SQL:

SELECT *
FROM my_table
WHERE
    end >= DATE( start - INTERVAL '16:00:00' HOUR_SECOND ) + INTERVAL '16:00:00' HOUR_SECOND + INTERVAL 1 DAY

Answer 2

select * from table where '16:00:00' between CAST(start AS DATE) and CAST(end AS TIME)
didn't check, but something like this should work

Answer 3

start_time < 16:00 AND end_time > 16:00 OR start_time > end_time
If you haven't missed anything, you can try something like this.

Answer 4

I propose to go from the opposite: in what interval does the time of 16 hours not fall? Only in one that fits entirely between 16 o'clock on the first and next day. That is, we need to check whether our interval falls into this interval. The intersection of the intervals [a, b]and [c, d]is easily verified by the condition a <= d && b >= c. In our case, start >= next_date_16_00 && end <= start_date_16_00, where start_date_16_00is 16 hours on the day the interval starts, and next_date_16_00is the same, but on the next day. We reverse the condition (after all, we started from the opposite): start < next_date_16_00 || end > start_date_16_00.
And our request:

SELECT *
FROM my_table
WHERE
    start < TIMESTAMP( DATE(start) + INTERVAL 1 DAY, '16:00:00' ) OR
    end > TIMESTAMP( DATE(start), TIME('16:00:00') )

Answer 5

And I would add a field " duration " to the table, (or instead of " end date ") AND have a query
((start time + duration) modulo 24) >= desired point

where MOD(`start`+`length`,24)>=16