Date Query "inclusive" logic

[mboynes]

When querying dates ranges using before and after, the date logic when 'inclusive' => true seems non-intuitive. Consider the following date query:

$date_query = array(
	'after' => array( 'year' => 2013, 'month' => 1 ),
	'before' => array( 'year' => 2013, 'month' => 3 ),
	'inclusive' => true
);)

I would expect this to search between 2013-01-01 00:00:00 and 2013-03-31-23:59:59, but it actually generates the following SQL:

( post_date >= '2013-01-31 23:59:59' AND post_date <= '2013-03-01 00:00:00' )

"Inclusive" is essentially only including one second of the most refined argument you pass (here, that's month). The logic is perfect when "inclusive" is set to false. It seems to me that when 'inclusive' => true, the $default_to_max parameter passed to WP_Date_Query::build_mysql_datetime() should be inverted. Patch attached which does this.

[mboynes]

Resolves date query before/after logic when "inclusive" is set to true

[oso96_2000]

Unit test and fixed diff route

[nacin]

I'm not positive we should change this. I think we should look at some other libraries to see how they handle this. Really to get what you want, you should set an inclusive start and an exclusive end of 2013-03-02 00:00:00. I am not familiar with how other libraries/ORMs handle this, though I do know that this is how Gmail filters work.

[Viper007Bond]

The change makes sense to me and was an oversight on my part.

The whole point of the inclusive parameter is so that you don't have to add or subtract one to your date/times in order to get them outside of the range you want to include.

[khromov]

This is a huge issue. It doesn't make sense that the default is exclusive both before and after. The edge case of midnight will result in a posts not being shown if you have two queries select from "day 1 to day 2" and "day 2 to day 3", and the inclusive case will make it show twice.

Edit
Is it possible to introduce "inclusive_before" and "inclusive_after" flags to cope with this?

Any other workaround available? Right now I'm doing

strtotime($end_date. '+1 second');

to get start date inclusive, end date exclusive...but it's not very pretty.

[oso96_2000]

Replying to khromov:

This is a huge issue. It doesn't make sense that the default is exclusive both before and after. The edge case of midnight will result in a posts not being shown if you have two queries select from "day 1 to day 2" and "day 2 to day 3", and the inclusive case will make it show twice.

Care to post an example of your query? I can search for a solution or an alternative

[Viper007Bond]

Replying to khromov:

Edit
Is it possible to introduce "inclusive_before" and "inclusive_after" flags to cope with this?

Any other workaround available? Right now I'm doing

strtotime($end_date. '+1 second');

to get start date inclusive, end date exclusive...but it's not very pretty.

This is a separate issue. Please open a new ticket for that suggestion.

[wonderboymusic]

Let's regroup on these early 4.0

[wonderboymusic]

This is too late in the cycle, we need to spend some more time in Unit Tests and make sure enough people properly understand how all of this stuff works before tossing it all in. I personally moved a lot of the Query tickets into 3.9 and want to see resolutions as much as anyone else...

[nacin]

Yeah, and for what's essentially a breaking change, we can't make this three weeks before a release. I'm okay with dropping it in early, preferably with a post on make/core as well to explain the change.

[wonderboymusic]

Patch still applies cleanly - let me know if anyone has any objections to this going in...

[helen]

Should happen now or be punted. No opinion here.

[DrewAPicture]

I think this has merit and seems like an oversight as mentioned in comment:7.

It is also, though, a breaking change as noted by Nacin in comment:13 so I'd prefer we add both an original @since for 3.7.0 as well as an @since 4.0.0 notating the change to the inclusive argument behavior.

[mboynes]

Replying to helen:

Should happen now or be punted. No opinion here.

Aside from the documentation additions as noted by DrewAPicture, is there anything we're waiting on for this? Are there any concerns not noted here?

[DrewAPicture]

+ docs

[DrewAPicture]

26653.diff​ adds the docs described in comment:17.

[SergeyBiryukov]

In 28935:

WP_Date_Query: The inclusive logic should include all times within the date range.

props mboynes, oso96_2000, DrewAPicture.
fixes #26653.