Improved date arguments for WP_Query

[Viper007Bond]

Currently there is no way to get a set of posts that fall within a date range, are older/newer than a certain date, etc.

A new set of arguments should be created for WP_Query that work similarly to the relatively new meta and taxonomy arguments.

Argument structure suggestions welcome.

I would love to see a date_query that allows for BETWEEN two dates (or years, or months, etc.), greater than or less than, IN, etc.

Sign me up. It would make it easier to use WP_Query in get_adjacent_post() - #17807 - calendar plugins, etc. etc.

Nacin and I have been brainstorming a bit and here's some requirements and potential argument formats that we've come up with.

'date_query' => array(
	// Find posts between two dates
	array(
		'before' => '2011-09-17 16:40:23',
		'after' => array(
			'year' => 2011,
			'month' => 8,
			'day' => 12,
		),
		'inclusive' => true, // Including exactly the dates
	),

	// Find posts NOT between two dates
	// due to before being less than after
	array(
		'before' => '2011-09-10',
		'after' => '2011-09-15',
	),

	// Find all posts after 2PM on any day
	array(
		'time' => '14:00:00',
		'compare' => '>',
	),

	// Find posts made during
	// Nacin's crazy work hours
	array(
		'after' => '8:30:00',
		'before' => '17:15:45',

		// Is this even possible?
		'dayofweek' => array( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ),
	),
),

The two range ones are easy -- basic MySQL.

The "after 2PM" example shouldn't be hard either. We're thinking you covert the time to a number, like 4:32:45 would turn into 4.3245. You can't independently compare hours and minutes because that last example would be greater than 30 and less than 15. You will have to merge hours, minutes, and seconds into a single number you can compare.

I have no idea if the day of the week thing is possible, but it's an interesting idea.

Great idea.

Could we get a 'field' parameter too? We have post_date, post_date_gmt, post_modified and post_modified_gmt which could all work with this query format. post_date would be the default of course.

Replying to Viper007Bond:

// Is this even possible?
'dayofweek' => array( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )

Should be able to do this with MySQL's DAYOFWEEK() function, eg. to select all weekend posts:

WHERE DAYOFWEEK( post_date ) IN ( 1, 7 )

Highlighting in case others hadn't spotted it - the first before/after is an AND, whereas the second before/after would be an OR.

I agree with the field argument too, since someone might want posts published before one date, and edited after another.

C/W/Should the arguments here also eventually cover dates stored in meta fields too?

I think I'm ditching string support. It's so much easier from a code perspective (hard to parse) and less ambiguous if you just pass an array of year, month, day, hour, minutes, and/or seconds.

Replying to johnbillion:

Could we get a 'field' parameter too? We have post_date, post_date_gmt, post_modified and post_modified_gmt which could all work with this query format. post_date would be the default of course.

Totally.

Replying to GaryJ:

C/W/Should the arguments here also eventually cover dates stored in meta fields too?

No, I don't think so. So much added complexity and I think the existing meta_query arguments should be able to handle most (but not all) of that kind of stuff.

I think I'm ditching string support. It's so much easier from a code perspective (hard to parse) and less ambiguous if you just pass an array of year, month, day, hour, minutes, and/or seconds.

Less ambiguous for sure, however less flexible and will end up with people having to re-invent the wheel processing timestamps themselves.

Something like this might be usable:

$a = array();
list($a['year'], $a['month'], $a['day'],$a['hour'],$a['minute'],$a['second']) = explode(',', date('Y,m,d,H,i,s', strtotime('today')));
//TODO: Filter out any items which are equal to 00 (h:i:s when only a date was specified)
var_dump($a);

has the advantage you could use "6 hours ago" as well :)

and I think the existing meta_query arguments should be able to handle most (but not all) of that kind of stuff.

Definitely agree, This can utilise date functions on a date column, meta fields are well, strings :)

Replying to dd32:

Something like this might be usable:

But what about 2011-09 ? Should that be September 1st, 2011 (which is what strtotime() will give you) or should it be any day in September (my intent). How do you know from the strtotime() response that it wasn't 2011-09-01 that was passed (a specific day rather than a specific month)?

Perhaps if is_string(), then just run it through strtotime() and use the result as-is. This provides 6 months ago and full MySQL date/time support but users will just have to realize that wildcards need to be passed as an array instead of a partial date/time string.

But what about 2011-09 ? Should that be September 1st, 2011 (which is what strtotime() will give you) or should it be any day in September (my intent).

If you want to be specific, you need to use a specific time format, simple :)

That being said, 2011-09 would work in some situations, just not every situation. After 2011-09 wouldn't work, as it'll include sept. Before 2011-09 would work fine though.

To me it sounds like a reasonable "shortcut" to prevent reinventing the wheel, it simplifies parsing for 99% of situations and if you're specific in your input, it'll respect it. Some people will get caught up by it doing things they don't expect if they're vague in what they request.

Preliminary patch for testing purposes, still a work in progress (see comments)

Okay, attached is a patch that contains what should be a fully flushed out WP_Date_Query class. The current integration into WP_Query is just preliminary and for testing purposes. It will need to be replaced with some code that uses WP_Date_Query to process the legacy date/time parameters. There's also a bit more PHPdoc work to be done.

For now though this patch will do nicely for testing and feedback which is what I'm really after at this point.

Here's some examples that you can use to test:

// Between 8:45 AM and 5:12 PM on weekdays
$query_test = new WP_Query( array(
	'date_query' => array(
		'column' => 'post_date', // Default
		'relation' => 'AND', // Default
		array(
			'hour' => 8,
			'minute' => 45,
			'compare' => '>=',
		),
		array(
			'hour' => 17,
			'minute' => 12,
			'compare' => '<=',
		),
		array(
			'dayofweek' => array( 2, 6 ),
			'compare' => 'BETWEEN',
		),
	),
) );

// Posted between 2-3 weeks ago
$query_test = new WP_Query( array(
	'date_query' => array(
		array(
			'after' => '3 weeks ago',
			'before' => '2 weeks ago',
		),
	),
) );

// Posted before March 1st, 2010
$query_test = new WP_Query( array(
	'date_query' => array(
		array(
			'before' => array(
				'year' => 2010,
				'month' => 3,
				'day' => 1,
			),
		),
	),
) );

// Posted during the 2 PM hour on a Sunday or during the 6 PM hour on a Monday or Tuesday
$query_test = new WP_Query( array(
	'date_query' => array(
		'relation' => 'OR',
		array(
			'hour' => 14,
			'dayofweek' => 1,
		),
		array(
			'hour' => 18, // Will be cast to an array
			'dayofweek' => array( 2, 3 ),
			'compare' => 'IN',
		),
	),
) );

Oh, a few more things:

• If before or after is set in a sub-query, then all other parameters will be ignored (month, hour, etc.). Perhaps it shouldn't be this way ("before XXX and month=5") but I only just thought of this now and you can likely accomplish that anyway with the current code.

• For before and after sub-queries, you can set inclusive to true if you want to include exactly the before/after value(s). Basically it turns > and < into >= and <= respectively.

Oh, and here's the SQL of the above examples:

AND ( ( DATE_FORMAT( wp_trunk_posts.post_date, '%H.%i' ) >= 8.450000 ) AND ( DATE_FORMAT( wp_trunk_posts.post_date, '%H.%i' ) <= 17.120000 ) AND ( DAYOFWEEK( wp_trunk_posts.post_date ) BETWEEN 2 AND 6 ) )

AND ( ( wp_trunk_posts.post_date > '2011-09-05 01:44:13' AND wp_trunk_posts.post_date < '2011-09-12 01:44:13' ) )

AND ( ( wp_trunk_posts.post_date < '2010-03-01 00:00:00' ) )

AND ( ( DAYOFWEEK( wp_trunk_posts.post_date ) = 1 AND HOUR( wp_trunk_posts.post_date ) = 14 ) OR ( DAYOFWEEK( wp_trunk_posts.post_date ) IN (2,3) AND HOUR( wp_trunk_posts.post_date ) IN (18) ) )

So cool. Great work.

This is awesome, thanks for the effort.

Thanks guys. :)

Also a few bugs I noticed:

• $valid_args array is out of date (missing some args) but that's just there to prevent notices and make life easier by filling in the arrays.

• $value = $this->build_value( $compare, $query['foo'] ) can return 0 which means it will be ignored. I think only the week parameters are affected here.

• Speaking of the week parameters, I should make use of _wp_mysql_week() in my code.

Possibly good to go, minus phpdoc. Needs testing!

My test file to make sure SQL statements are being correctly generated

Okay! Minus a little bit of missing phpDoc, I think this might be good to go. This is certainly not WordPress 3.4.0 material because it will need major testing but maybe it can go in early into 3.5.0 or something.

The legacy individual date/time parameters have been ported over to the new class and appear to be working the same, but this needs to testing to make sure it's 100% backwards compatible. Not sure how to design unit tests around this.

I also wasn't sure the best way to integrate it into WP_Query since WP_Tax_Query and WP_Meta_Query are both integrated differently, but I took a stab at it.

Thoughts, comments, and feedback is desired! :)

For consistency with the other query classes, get_sql() should return an associative array.

Also, I don't think the other methods should be public.

But a bigger issue is that it's currently limited to the wp_posts table. It should be usable with any table.

Replying to scribu:

For consistency with the other query classes, get_sql() should return an associative array.

They only do that because they need to modify both the JOIN and the WHERE. My stuff is strictly WHERE-based so there's no need to return array( 'where' => $sql_string ) is there?

But a bigger issue is that it's currently limited to the wp_posts table. It should be usable with any table.

I hadn't thought of making it support comments. That's a great idea.

They only do that because they need to modify both the JOIN and the WHERE. My stuff is strictly WHERE-based so there's no need to return array( 'where' => $sql_string ) is there?

Speaking of which, it would be useful if we could treat custom fields as date columns. For example, there are many 'event' post type definitions, with two custom fields: 'start_date', 'stop_date'.

Replying to scribu:

Speaking of which, it would be useful if we could treat custom fields as date columns. For example, there are many 'event' post type definitions, with two custom fields: 'start_date', 'stop_date'.

Yeah, I guess that's possible. WHERE HOUR( post_meta.meta_value ) = 1 or whatever.

Final patch? Adds date query support to posts and comments

I believe the above patch is ready to go into core.

It adds support for querying both posts and comments based on date parameters:

$query = new WP_Query( array(
	'date_query' => array(
		'column' => 'optional, column to query against, default is post_date',
		'compare' => 'optional, see WP_Date_Query::get_compare()',
		'relation' => 'optional, OR or AND, how the sub-arrays should be compared, default is AND',
		array(
			'column' => 'see above',
			'compare' => 'see above',
			'after' => 'string or array, see WP_Date_Query::build_mysql_datetime()',
			'before' => 'string or array, see WP_Date_Query::build_mysql_datetime()',
			'inclusive' => 'boolean, for after/before, whether exact value should be matched or not',
			'year' => '4 digit int',
			'month' => 'int, 1-12',
			'week' => 'int, 0-53',
			'day' => 'int, 1-31',
			'hour' => 'int, 0-23',
			'minute' => 'int, 0-60',
			'second' => 'int, 0-60',
		),
	),
) );

$comments = get_comments( array(
	'date_query' => array(
		// ...
	),
) );

Not supported is querying against post meta values however the class is specifically written to be able to query against any database table or column. WP_Meta_Query just needs to be updated to use this new class and I wasn't sure the best way to go about doing that argument wise.

Legacy date/time parameters are handed by this new class and it's important that testing be done to make sure that queries function the same way as before.

Please apply the patch, test, and post your feedback! :)

Incoming test file next.

Generates some sample queries and provides some examples

This is in dire need of unit tests.

Over the weekend, I learned the magic of unit tests and managed to install PHPUnit. I'll see if I can get some unit tests going.

Working on unit tests over at ​#UT126.

When passing an array to before, items default to the current year and the minimum values for everything else.

For after, perhaps these values should default to the maximum, for cases like this:

'date_query' => array(
	array(
		'after' => array(
			'year'  => 2009,
		),
	),
),

Currently that would mean after January 1st, 2009 rather than anything from the year 2010 or newer, which could be confusing.

Replying to Viper007Bond:

For after, perhaps these values should default to the maximum

I think that makes a lot more sense and is what users will expect from what the syntax implies.

I think I'm happy with this latest patch and I've written 39 unit tests (which are attached). The non-date_query tests, i.e. legacy date/time parameters, pass on both unpatched and patched trunk WordPress meaning there's (hopefully) no regressions.

I believe Nacin wanted to get this into 3.6 early so proper real-world testing could be done.

Okay, we'll be testing this out over the next few days. Will report back if we encounter any issues. Would like to see this get in early, too.

Nacin pointed out some flaws in my code. For example I should use a whitelist for the column name and I missed a legacy date argument. I need to refresh my patch when I get a chance (probably too late for 3.6 anyway).

That said it could still use testing as the SQL itself should be finalized.

Thanks for the update. It's running well right now on 10,000+ queries (overnight cron task). So far we haven't encountered any issues and the performance seems good. If you want to post further details of Nacin's feedback here, I can probably do the refresh if it's clear what needs to be done.