Check if some something else is within date range

nzkiwi80

This seems easy at first but I'm not sure if is.

I've got a window of time.

6th Dec 3.00pm to 9th Dec 9.00am

I want to check my database (datetime column) so see if there are any rows which have starts overlapping.

So startDate after 6th Dec 3pm but only important if earlier than 9th 9am.. if it starts earlier then 6th it needs to have a an end date before the 6th as well.

Any help

Weedpacket

Do you mean you want to check to see if '2023-12-6 15:00' <= startDate and startDate <= '2023-12-9 09:00'?

nzkiwi80 it needs to have a an end date before the 6th as well.

Oh, so it's not just about the start. Well, to verify that two intervals don't overlap you have to verify that the end date of one is before the start date of the other: thisEnd < thatStart or thatEnd < thisStart. Presumably you have enough integrity checks in your data to ensure that an interval can't end before its own start.

nzkiwi80

Yes that's right, I'm doing this to check if any rows exists in a database before inserting a new row. So I'm wanting to count the number of rows based on my inputted start & end date.

Essentially only having one row for a period of time. Yes, I'm also checking the start if before the end before doing anything with my database.

I just can't have rows where dates overlap

So let's say i had one row like below
Start | End
3rd Dec - 10thDec

All three of these should return a count of 1 based on the above
Start | End
9th Dec - 18Dec
1stDec - 4th Dec
2nd Dec - 13th Dec

Weedpacket

Well, I don't know what you're "counting", but in all three examples, comparing the interval with the given interval, both start dates are before both end dates, so they overlap. (The opposite, where the end date of either interval is before the start date of the other, would mean they don't overlap.)
(Note too that standard SQL has an (start1, end1) OVERLAPS (start2, end2) operator which does pretty much that.)

nzkiwi80

Weedpacket

Counting the number of rows where there's a match, it should be zero

What I'm thinking is;

SELECT * FROM table WHERE startDate >= $sd AND endDate <= $en

But if my start is great than SD but less than ED (startdate and enddate) then I might get a valid (invalid) match.

OVERLAPS is SQL, I'm using mysql

Weedpacket

As long as one finishes before the other starts (i.e. if the end date of either interval is before the start date of the other) then the two intervals don't overlap.

nzkiwi80

I I've got this..

Only one more scenario to cover and that's if the the start and end date are not between but before and after and during the time there's a match

WHERE

'2023-12-05 11:39:00' BETWEEN DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i:%Sj') AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i:%Sj')
OR
'2023-12-09 11:39:00' BETWEEN DATE_FORMAT(StartDate, '%Y-%m-%d %H:%i:%Sj') AND DATE_FORMAT(EndDate, '%Y-%m-%d %H:%i:%Sj')

sneakyimp

nzkiwi80

Your post could be more clear about what you are checking. Your original post says:

I want to check my database (datetime column) so see if there are any rows which have starts overlapping.

You suggest that StartDate is a datetime column, in which case I don't think you should be using DATE_FORMAT on those values because that will convert StartDate and EndDate from their very useful datetime formats (which are very easily compared) with a string, in which case you are doing a string comparison, which is not what you want, i don't think -- although it may work.

You also don't specify what DBMS you are using. If you are using MySQL, you might want to Read The Friendly Manual on the datetime type and do a bit of experimentation. MySQL should be able to recognizes 2023-12-06 03:00:00 and 2023-12-09 09:00:00 as date specifiers and can probably perform the comparison with your StartTime and EndTime colums directly.

Weedpacket

sneakyimp MySQL is mentioned; but I get the feeling that something is not being explained (certainly it's not being explained well). I think it's about ensuring a given interval doesn't overlap a set of existing intervals, but all my suggestions about how to do that are being ignored, so maybe I'm wrong.

sneakyimp

Weedpacket MySQL is mentioned

Aha I had overlooked it. I hope I can be forgiven for not reading so closely. The responses to your (very helpful) posts do seem to be rather confused.