Filtering data with WP_Date_Query Entrails

Filtering data with WP_Date_Query Entrails

Online by J&S Code
14 July 2017 | PHP WP snippets, code & hacks | 259 recognitions

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 data with WP_Date_Query Entrails

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 July 2017:

Example 2 – Between January to July:

Example 3 – In 2016, between February-July, but not on April:

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 2016 and June 6st 2017:

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:

Wrap up

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!

About The Author

J&S Code

Sorin C is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

Opinions

Great tutorial, good for introduction to working with WP_Date_Query.

    I’m glad you enjoyed the post.

Put here your thoughts

Your email address will not be published. Please complete all fields * correctly You may use these HTML tags: bold text, italicised text, and paragraphs.

On the same idea

How to Remove HTML Tags from WordPress Comments
Online by stevenmedia | 8 November 2017

Nowadays, a lot of bloggers choose to remove HTML tag from WordPress websites. However, for beginners and newbies in this field, they may feel confused about the practice. Meanwhile, some...

How to create a WordPress child theme
Online by stevenmedia | 1 October 2017

The WordPress platform is a magnet for those who want to take matters into their own hands, who want complete control over their websites and want to be independent in...

Useful WordPress Hook hacks
Online by stevenmedia | 30 September 2017

Hooks are very useful in WordPress. They allow you to “hook” a custom function to an existing function, which allows you to modify WordPress’ functionality without editing core files. In...