WP_Date_Query class for generating SQL
WHERE clauses to filter queries by date. We worked very hard to build an easy-to-use tool to help developers generate multiple date queries. Introducing our new WP_Date_Query Generator.
Filtering data with WP_Date_Query Entrails
Filtering Posts by Date
The new WP_Date_Query class accepts time-related parameters (
second), multiple arrays with
compare operators, and multiple arrays with other nested date queries. You can create simple date-based filters and very complex filters, this class provides solutions for almost every scenario.
There are 3 main ways to use the date query: (1) Create an array of simple date parameters. (2) Use a range of dates with before/after parameters. (3) Use relative date ranges with textual descriptions. This may sound complicated but its not and actually very easy to use once you get the hang of it.
The Date Query Generator
We spent a lot of time to create an easy-to-use interface for our new Date Query Generator. This tool suitable for newbie developers and advanced coders. It allows you to create custom date queries, save them to your account and integrate to other queries. For example, you can create a date query and save it to your account, then you can create a new date query and integrating the first snippet into your new query, this is going to allow you to generate nested date queries with complex very roles.
WordPress supports nested queries. But we had to think of a way to support this in our generators. Now that this challenge is behind us, we will be implementing this on our other generators to provide you with even more advanced tools.
Specific Date Parameters
As mentioned above, the simple date parameters are
second. They all accepts integers as values.
Those parameters will allow you to filter your posts (or any other CPT) by any date/time, using the
compare parameter which accepts
NOT BETWEEN. You can also create several date filters and set
relation parameter to
AND between those date queries.
Example 1 – Items in July 2017:
$date_query = array( array( 'year' => 2017, 'month' => 7, ), );
Example 2 – Between January to July:
$date_query = array( 'relation' => 'AND', array( 'month' => 1, 'compare' => '>=', ), array( 'year' => 7, 'compare' => '<=', ), );
Example 3 – In 2016, between February-July, but not on April:
$date_query = array( 'relation' => 'AND', array( 'year' => 2017, ), array( 'month' => 2, 'compare' => '>=', ), array( 'month' => 7, 'compare' => '<=', ), array( 'month' => 4, 'compare' => '!=', ), );
The simple date parameters are used for specific dates. For Date Range, we will use an array with
after parameters. Note that those two range parameters support only
days. Unlike the specific parameters, the range parameters are don’t support time-related parameters (hours, minutes, seconds) and other week-related parameters.
Example 4 – After September 11th 2011:
$date_query = array( array( 'after' => array( 'year' => 2011, 'month' => 9, 'day' => 11, ), ), );
Example 5 – Between January 1st 2016 and June 6st 2017:
$date_query = array( array( 'before' => array( 'year' => 2017, 'month' => 6, 'day' => 30, ), 'after' => array( 'year' => 2016, 'month' => 1, 'day' => 1, ), 'inclusive' => true, ), );
Relative Before/After Range using Textual Description
This is a really cool feature! You can use textual date/time description to limit time periods. Unlike the examples above witch use pre-defined time periods, the textual description allows you to use relative dates based on current time. You no longer need to use PHP code to filter dates using date() function!!!
Textual description works great with any specific date like “
16 November 2016” or “
May 2010“. But the real benefit’s of using textual date descriptions is for relative dates like “
last week“, “
next month“, “
2 years ago“, “
next monday“, “
midnight -7 days“. You can also combine several relative dates like “
+1 week 2 days 4 hours“.
Example 6 – Filter items between January and February OR items from the last two months:
$date_query = array( 'relation' => 'OR', array( 'before' => array( 'month' => 2, ), 'after' => array( 'month' => 1, ), 'inclusive' => true, ), array( 'after' => '2 months ago', ), );
Example 7 – Filter items from the last year, not published in Sunday’s:
$date_query = array( 'relation' => 'AND', array( 'after' => '1 year ago', ), array( 'dayofweek' => 1, 'compare' => '!=', ), );
As mentioned above, one of the really great features of this class is that it supports nested queries – meaning that you can include one set of rules inside an other. This allows developers run very complex filters on large databases, replacing the need of writing SQL queries. The class generates the SQL, you just set the rules.
Our generator supports nested queries by loading other saved date query snippets. You need to login to your account (free registration), and save a few date query snippets. When you choose the date type, you will see a select box with all your saved date queries.
Example 8 – Either query from example 6 OR example 7:
$date_query = array( 'relation' => 'OR', array( 'relation' => 'OR', array( 'before' => array( 'month' => 2, ), 'after' => array( 'month' => 1, ), 'inclusive' => true, ), array( 'after' => '2 months ago', ), ), array( 'relation' => 'AND', array( 'after' => '1 year ago', ), array( 'dayofweek' => 1, 'compare' => '!=', ), ), );
For conclusion, the date query generator is a new free tool to filter content by date/time. It makes it easy to generate SQL statements “the WordPress way”. And it allows you to create very complex filters. Use it to create date filters for your projects!