Date Queries In WordPress 3.7+

Posted on by

Earlier today, a patch for WordPress that I’ve been working on got committed to WordPress trunk. “Trunk” is the in-development version of WordPress and will eventually become the next version of WordPress, in this case 3.7.

My patch introduces the ability to do complex date-based queries for fetching both posts and comments from the WordPress database. In the past, you could select posts that had a specific value for year, month, etc. but there was no way to do things like selecting all posts before (or after) a certain date or selecting all posts between two different dates. With my patch, this and more is now easily possible.

Here’s some examples:

// Get the 10 most recent posts made
// between 9AM and 5PM on weekdays
$some_posts = new WP_Query( array(
	'date_query' => array(
		array(
			'hour' => 9,
			'compare' => '>=',
		),
		array(
			'hour' => 17,
			'compare' => '<=',
		),
		array(
			'dayofweek' => array( 2, 6 ),
			'compare' => 'BETWEEN',
		),
	),
	'posts_per_page' => 10,
) );

// Get all posts from this summer
// June 1st to August 31st, inclusive
// Note that strtotime()-compatible strings can be used
$some_posts = new WP_Query( array(
	'date_query' => array(
		array(
			// String via strtotime()
			'after'     => 'June 1st, 2013',
			// Or if you want, an array
			'before'    => array(
				'year'  => 2013,
				'month' => 8,
				'day'   => 31,
			),
			'inclusive' => true,
		),
	),
	'posts_per_page' => -1,
) );

// Any posts made over a year ago
// but modified in the past month
$some_posts = new WP_Query( array(
	'date_query' => array(
		array(
			'column' => 'post_date_gmt',
			'before' => '1 year ago',
		),
		array(
			'column' => 'post_modified_gmt',
			'after'  => '1 month ago',
		)
	),
	'posts_per_page' => -1,
) );

It works for comments too:

// All comments from post ID 123
// that are within the past week
$some_comments = get_comments( array(
	'post_ID' => 123,
	'date_query' => array(
		array(
			'after' => '1 week ago',
		),
	),
) );

As you can see, the possibilities and combinations of cool things you can do are endless.

Here’s all of the possible arguments:

'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',
	),
	array(
		...
	),
	..
),

Additionally, all of the old-school date and time arguments for WP_Query are now handled by my code as well. They will continue to work as before and you only need to use the date_query parameter if you want more advanced control of your results.

Questions? Anything you want me to clarify? Leave a comment below. 🙂

98 thoughts on “Date Queries In WordPress 3.7+”

    • Yes, this has relation (see the documentation code block in the post). I used meta_query for inspiration and guidance. However like tax_query and meta_query it can only be applied overall — either all conditions are AND to each other or they are OR. You can’t mix.

      If you want to do more complex things then you’ll have to filter the SQL. You can make a new instance of WP_Date_Query and have it help you out with constructing the SQL though.

      • OK cool, thanks! Was just curious because I’ve been considering adding a patch for nested ‘relation’ arrays, so I now need to look at this new class for what implementation would take there too.

        • We considered nested arrays for relations when doing the original tax_query stuff (to allow mixing of OR and AND cases), but I believe people thought it too complex and to have somewhat limited use cases. In the end, I think if you’re making relationships that complex, you might be outside the primary use case of “meta” and should probably consider making your own table instead.

          • I may end up adding a filter or two for my purposes, so my project can extend WP_Query meta_query handling to support nested arrays. Pods currently supports nested arrays for it’s own ‘where’ handling, and it would be great to give our users that option too. We’ll be hooking into WP_Query for certain meta fields which will actually be mapped to their own table in some cases.

    • No, it does not. This would fall under an enhancement of the existing meta_query argument rather than falling under my new date_query argument. My WP_Date_Query class could be used to for this though as all it does is generate WHERE clauses for the specified column.

      Open a ticket if this is something you want. 🙂

        • No problem. I wrote it as a generic SQL helper class so that it can be used for pretty much anything, I just only added support for the posts and comments tables for now as those were low hanging fruit and pretty straightforward.

  5. Considering this would have required modifications to WP_Query::get_posts what measures did you take to prevent an increase in NPath complexity despite adding a new feature? ( In 3.6 it has an NPath complexity of 1,435,733,941,397,422,709,124,940,625,188,500,371,668,992,000,000 with a recommendation of 200 or below )

    • Sorry, but I don’t know what you’re talking about. This simply adds a single new argument to WP_Query::get_posts(). If you don’t use the parameter, then queries stay as simple as before. It’s not adding overhead or query complexity if you don’t use it. If you do use it, then it’s using MySQL’s built-in date and time functions to do the hard work which should allow normal optimizations by MySQL.

      The change to the class method can be found here:

      http://core.trac.wordpress.org/changeset/25139/#file2

      If you have concrete recommendations for improvements to my code, I am more than willing to hear them and incorporate them into my code. Please leave them over on Trac instead of here though as that’s the canonical discussion location:

      http://core.trac.wordpress.org/ticket/18694

        • I’ve heard of NPath before but that’s about it. My lack of formal education is failing me here. I went to school for mechanical engineering before realizing that wasn’t for me. 🙂

          Anyway, all of the code that does this work is off on its own inside of a class, outside of WP_Query. The only new code added to WP_Query::get_posts() is literally an if statement or two, checking to see if the parameter is set.

          Now if it is set, then the new WP_Date_Query class gets called and that’s when things get a little more complex. It is abstracted out into various class methods in an effort to keep things simple but it is also not overly abstracted just for the sake of keeping the number of conditionals low.

          Feel free to judge for yourself. The code can be found here:

          http://core.trac.wordpress.org/browser/trunk/src/wp-includes/date.php

  8. Pingback: Date queries in WordPress 3.7 : Post Status

  9. I saw the patch being committed and thought just how epic it really was. I’ve already got ideas for how to make use of it. This should make my Timeline plugin look a little more snazzy! 🙂

  14. That’s great news and well done mate. It’s actually kinda strange that this was not added to core a long time ago. This will definitely come in handy. 🙂

  17. Pingback: A practical use for the new date queries in WP 3.7

  19. Alex, the WP_Meta_Query used in WP_Query, WP_Comment_Query and WP_User_Query.
    What about your new WP_Date_Query, did you implement it only in WP_Query ?

  21. Pingback: ?? WordPress 3.7 ??? WP_Date_Query | ????

  22. Pingback: WordPress 3.7 ?????????? WP_Date_Query | WordPress??

  24. Pingback: wp-tricks.co.il ??????? ??????? ???????? 3.7

  25. I need this in my mouth RIGHT NOW. 3.7 cannot come soon enough! This is a lovely way to handle date-based queries, and funnily enough fulfils my exact requirements at this very moment (which is kinda how I ended up here in the first place). Kudos!

  26. Pingback: Endlich! Date Queries! Funktion "Zeige Posts von Datum A bis Datum B" bald im Core! | WP-Entwickler.at

  27. Pingback: WordPress 3.7 Introduces Advanced Date Queries

  28. Pingback: WordPress 3.7 ?????????? WP_Date_Query | WP??

  29. Pingback: What's new in WordPress 3.7, "Basie" : Post Status

  30. Pingback: What’s new in WordPress 3.7 | Business & Technology

  31. Pingback: Separatista

  32. Pingback: WordPress 3.7 Now Available :: WebDesign.com

  33. Pingback: What’s new in WordPress 3.7, “Basie”

  34. Pingback: wp-tricks.co.il ??????? 3.7

  36. It´s really awesome! Thank you so much!

    I was wondering if there is a way to query posts like query the last 3 posts of the current day and after “of the current date”.?

    Thank you

  37. Pingback: WP Magnet | A look into: WordPress Date Query

  38. Pingback: ?????? ??? – ????? ??????????? – ??????? ? ????? ????????? ? ??????? ? ??????? » A look into: WordPress Date Query

  39. Pingback: A look into: WordPress Date Query | RichInfoWorldRichInfoWorld

  40. Pingback: A look into: WordPress Date Query | OGM Français - Une vue terrifiante de l'Asie

  41. Pingback: A look into: WordPress Date Query – Supreme #WordPress Blog | Supreme Factory

  42. Pingback: A look into: WordPress Date Query | DesignNews

  43. Pingback: A look into: WordPress Date Query - California King Sets

  44. Pingback: Entendendo o Date Query no WordPress 3.7 | Super agregador

  45. Pingback: A Look Into: WordPress Date Query : wpimp.com

  46. Hi Alex,
    I was wondering if there is a way to query posts like this

    show posts only if today is between post_date and post_date+15 days

    ?

    Thanks

  48. This is awesome.

    By the way, handling events (as cpt) and using ‘start date’ and ‘end date’ as custom fields; can I compare ‘start date’, ‘end date’ and ‘current date’ – so only events which are running or has the last day today or are in future can be shown.

    I am using this approach currently (Line 47-48 : https://gist.github.com/vajrasar/8158758) but this is fetching all posts and than filtering posts to display/or not. I know this is wrong, so was wondering if I can do this with WP_Query.

    Thanks.

  49. Pingback: A Look Into: WordPress Date Query

Comments are closed.