Date Queries In WordPress 3.7+

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. :)

95 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.

        • 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. :)

  1. 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

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

  3. 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. :-)

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

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

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

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

  8. 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!

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

  10. Pingback: WordPress 3.7 Introduces Advanced Date Queries

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

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

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

  14. Pingback: Separatista

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

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

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

  18. 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

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

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

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

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

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

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

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

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

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

  28. 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

  29. 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.

  30. Pingback: A Look Into: WordPress Date Query

  31. Pingback: How to list most commented posts with the new date queries in WordPress 3.7+ | Daniele Milana

  32. Hi. I’ve just started playing with WordPress development and I was looking for a way to get comments according to a specific week. Your code is very useful, thanks for that. But a question, how come this is nowhere in the WordPress documentation?

  33. I need a date query to return posts published between X days prior to post date of current post, and the post date of current post (excluding current post).

    Requires a static date range because this is for an email newsletter template. Newsletter will be sent weekly, as a digest of previous week’s posts, and will also be archived as a post, so it must retain correct posts for its’ week.

    • That’s pretty simple to do. Here’s some completely untested code that will likely work:

      // Plenty of other ways to get the "current post"
      // This is just an example
      $current_post = get_post( 123 );
      
      // How many days back?
      $days_back = 7;
      
      // date_query don't accept Unix timestamps currently
      // so we have to use date() to make it a string again
      $after_date = date( 'c', strtotime( "{$days_back} days ago", mysql2date( 'U', $current_post->post_date_gmt ) ) );
      
      $some_posts = new WP_Query( array(
      	'date_query' => array(
      		array(
      			'before' => $current_post->post_date_gmt,
      			'after' => $after_date,
      			'column' => 'post_date_gmt',
      		),
      	),
      	'post__not_in' => array( $current_post->ID ),
      	'posts_per_page' => -1,
      ) );
  34. Hello Alex, I tried to get posts between 2009 and 2010 inclusive the years. So I would expect all posts from 1st January 2009 to 31st December 2010.

    So the ‘date_query’ parameter is:
    array(1) {
    [0]=>
    array(3) {
    ["after"]=>
    array(1) {
    ["year"]=>
    int(2009)
    }
    ["before"]=>
    array(1) {
    ["year"]=>
    int(2010)
    }
    ["inclusive"]=>
    bool(true)
    }
    }

    but the WP_Query Class renders a SQL string within the WHERE clause:

    … WHERE… post_date >= ‘2009-12-31 23:59:59′ AND post_date = ‘2009-01-01 00:00:00′ AND post_date <= '2010-12-31 23:59:59' )

    Did I misunderstood the after-before-inclusive parameters? Or is there another way?

  35. There is an error in my last post: The rendered SQL string is

    … WHERE … post_date >= ‘2009-12-31 23:59:59′ AND post_date = ‘2009-01-01 00:00:00′ AND post_date <= '2010-12-31 23:59:59' )

  36. Pingback: What's wrong with my date_query? « Bay Area Web Design Bay Area Web Design

  37. Pingback: Easy Wordpress date-queries | Code and the Gang

  38. Pingback: Part three

  39. Pingback: part 8

  40. Pingback: PHP Function to Find Last Monday | var_dumpster

  41. Pingback: ??WordPress????#5??????? | ??????

  42. Pingback: A Look Into: WordPress Date Query | vienergie

  43. Pingback: WordPress 3.7 Now Available | Articles From An Actual Living Web Professional

  44. Hi Alex. I need to show 2 posts (one previous posts from today and next post from today) but I can’t figure out how to do it. I figure out how to get current month posts. Can you help me? Thanks in advance.

Leave a Reply