Blog

WP_Date_Query Entrails

WordPress uses 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 Posts by Date

Some WordPress queries such as WP_Query had an historical date filtering support. WordPress 3.7 standardized the date filtering mechanism using a helper class to be used in other WordPress queries.

The new WP_Date_Query class accepts time-related parameters (year, month, day, week, dayofweek, dayofweek_iso, hour, minute, 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 year, month, day, week, dayofweek, dayofweek_iso, hour, minute and second. They all accepts integers as values.

 

Simple Date Query Parameters
Simple Date Query Parameters

 

Those parameters will allow you to filter your posts (or any other CPT) by any date/time, using the compare parameter which accepts =, != , >, >= , <, <=, IN, NOT IN, BETWEEN and NOT BETWEEN. You can also create several date filters and set relation parameter to OR / AND between those date queries.

 

Example 1 – Items in May 2016:

 

Example 2 – Between January to May:

 

Example 3 – In 2016, between February-November, but not on May:

 

Before/After Range

The simple date parameters are used for specific dates. For Date Range, we will use an array with before and after parameters. Note that those two range parameters support only years, months and days. Unlike the specific parameters, the range parameters are don’t support time-related parameters (hours, minutes, seconds) and other week-related parameters.

 

WP_Date_Query Before After Range
WP_Date_Query Before After Range

 

Example 4 – After September 11th 2011:

 

Example 5 – Between January 1st 2015 and December 31st 2016:

 

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“, “now“, “next monday“, “midnight -7 days“. You can also combine several relative dates like “+1 week 2 days 4 hours“.

 

WP_Date_Query Textual Description
WP_Date_Query Textual Description

 

Example 6 – Filter items between January and February OR items from the last two months:

Example 7 – Filter items from the last year, not published in Sunday’s:

 

Nested Queries

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.

 

Nested WP_Date_Query
Nested WP_Date_Query

 

Example 8 – Either query from example 6 OR example 7:

 

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!

Rami Yushuvaev

I'm an entrepreneur, a web developer and a blogger. I’ve contributed code to each and every release since WordPress 2.8. I'm the Hebrew GTE responsible for the translation and the release of WordPress Hebrew version. The founder GenerateWP.com and several other WordPress related projects. I work mainly with Israeli startups, providing plugin development services.

One comment:

  • firefly2000

    This sounds really cool. Thanks for the introduction to WP_Date_Query; I can’t believe I had never heard of it before! I imagine I’ll be using it to create some nice custom search features.

    Reply

Leave a Reply