Be Excellent To Each Other

This is probably dead easy, but I just can't engage my brain this morning.

I have a table that looks something like the below (some fields omitted, but they aren't relevant)



Basically, I want to return the number of days in any given row that fall between a user specified date range.

So, for example, if the user enters a startdate of 2014-05-13 and an enddate of 2014-05-19, that will match row id 1, and the query should tell me that there are three days that match the user's date range (17th, 18th, 19th)

Is that even possible?

http://www.w3resource.com/mysql/date-an ... nction.php

Any good?

I think that'll just do the number of days between two dates, but not what I need which is the number of days that intersect between two different date ranges.

Also, from that link, why the hell are people rating MySQL functions? 8 out of 10 from 11 ratings.

Select between should help you. Select between on the start date, select between on the end date. Any rows that appear in both sets are a match.

I think your example is wrong, as the dates you give aren't falling between anything.

But I think you want

I don't think that'll do it, that will give me the number of days between the startdate and enddate in the table.

The example I gave is correct, I must have worded it badly.

In the table is the range 2014-05-17 to 2014-05-24 (7 days)

User enters 2014-05-13 to 2014-05-19 (6 days)

What I need is a query that will take the User's date range, and tell me how many days in that range match up with days in the table range.

So the User's date range matches up with the table's range on 3 days - 2014-05-17, 2014-05-18 and 2014-04-19 (because those three days are on row 1, and are in the user's date range)

Does that make any more sense?

It shouldn't be too expensive to do it by fetching the matching records and then individually counting days that match using a PHP loop, as there will never be that many data sets to worry about - but I'd prefer to avoid it if I can.

Oh, sorry - I was going for 'which rows can accomodate all the user's dates'.

I think you want

DATEDIFF(
GREATEST( user_start_date, row_start_date ),
LEAST( user_end_date, row_end_date )
)

I think that's right - the number of days between the later of the two start dates and the earlier of the two end dates.

Okay, try this.


[edit]I think this one's a little cheaper

You beauties. Cheers.

//edit: Needed to multiply the result by -1 to get a positive number that I could work with, but it did the trick.

You could have just swapped the order of the params in the DATEDIFF, but it's hardly going to make a big difference to the speed of the thing.