MarketPress

Blog

Back to the future: How WP_Date_Query works

In Plugins, Tutorial 15 Comments

With WordPress 3.7, we got the “date search”. In this post I will give you a short introduction and show some code examples.

New class WP_Date_Query

The new class is called WP_Date_Query. With its method get_sql() we can generate WHERE clauses to search for dates. The following parameters are available:

Parameter Type Description
year integer 4 digit year (e.g. 2011)
month|monthnum integer Month number (from 1 to 12)
week|w integer Week number (from 0 to 53)
day integer Day of the month (from 1 to 31)
hour integer Hour (from 0 to 23)
minute integer Minute (from 0 to 59)
second integer Second (from 0 to 59)
after string|array Search “after date X”
before string|array Search “before date X”
dayofweek string|array Day of the week (1 = Sunday, 2 = Monday, … 7 = Saturday)
dayofyear string|array Day of the year (from 1 to 366)
compare string =, !=, >, >=, <, <=, IN, NOT IN, BETWEEN, NOT BETWEEN (default: =)
column string post_date, post_date_gmt, post_modified, post_modified_gmt,
comment_date, comment_date_gmt (Default: post_date)

Not that nice are the doubled parameters for week ( week/ w) and month ( month/ monthnum). You can choose between these two, but if you are setting both, both will be used within the WHERE clause. I already reported this bug, and at least in WordPress 3.8 this should be fixed.

Another problem is the lack of sanitation: If you, for example, ask for the 370th day, or the 73rd minute, wrong or at least useless SQL will be generated. This bug #25834 should be fixed very soon too.

Additional “secret” Parameter

Reading the class, I found dayofyear. Another parameter that is not documented yet. This one allows you to search for a specific day of the year.

Inclusive Parameter

A special one is the inclusive parameter. This allows you an exact search with after and before. It sets less to less-or-equal and greater than to greater-or-equal. The boolean declaration is not correct, because the check of the parameter is done by the PHP function empty. If I would choose the value egal, this will be treated like true.

before and after

For the values of the parameters before and after, two different types of arguments are allowed.

Version 1: Array

In the extended version an array can be used with the following values:

Parameter Type Description
year integer 4 digit year (e.g. 2011)
month integer Month number (from 1 to 12)
w integer Week number (from 0 to 53)
day integer Day of the month (from 1 to 31)
hour integer Hour (from 0 to 23)
minute integer Minute (from 0 to 59)
second integer Second (from 0 to 59)

And again we have a little inconsistence: monthnum, week, dayofmonth and dayofyear are missing.

Version 2: Date as text

For the second one, we can search for a date with text. The following values are examples:

  • 10 September 2013
  • -1 day
  • -1 week

This is quite useful, if you don’t want to calculate. Instead you can just search for “last year”, “last week” or “2 days ago”. A list of allowed strings can be found in the official PHP documentation.

Examples

Generate a WHERE clause for all posts changed before July the 1st 2013 and between the 2nd and 6st day of the week.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$query_args = array(
array(
'dayofweek' => array( 2, 6 ),
'compare'   => 'BETWEEN',
),
array(
'before'    => 'July 1st, 2013'
),
'relation' => 'AND'
);
$query = new WP_Date_Query( $query_args, 'post_modified' );
echo $query->get_sql();  
 
// AND ( ( DAYOFWEEK( post_modified ) BETWEEN 2 AND 6 ) ) AND ( post_modified < '2013-07-01 00:00:00' ) )

Extending WP_Query / get_posts

The class WP_Query was extended by the parameter for internal calls with WP_Date_Query. We have the following two options for our data query to make the date search more detailed: simple and extended search.

Simple Search

The simple search works almost like a post_meta search. The following parameters can be used by the constructor of WP_Query to filter for dates:

Parameter Type Description
year integer 4 digit year (e.g. 2011)
monthnum integer Month number (from 1 to 12)
m integer year-month-combination (e.g.: 201307)
w integer Week number (from 0 to 53)
day integer Day of the month (from 1 to 31)
hour integer Hour (from 0 to 23)
minute integer Minute (from 0 to 59)
second integer Second (from 0 to 59)

Here we are missing some parts: We cannot use the parameter month like in the extended search (see below). We use monthnum instead. The same for the week: we have to use w instead of week. Also the value for search_column (default: post_date) and the compare parameter (default: =) are absent.

Extended Search

The extended search is a multi-dimensional array (like meta_query) to create complex searches with multiple dependencies and comparisons. The following parameters can be used:

  • date_query ( array)
    • relation ( string) – OR oder AND (default: AND)
    • compare ( boolean) – =, !=, >, >=, <, <=, IN, NOT IN, BETWEEN, NOT BETWEEN (default: =)
    • column ( string) – Row: post_date, post_date_gmt, post_modified, post_modified_gmt, comment_date, comment_date_gmt (default: post_date)

For the parameter list all of the above can be used. To connect those parameters, the relations AND or OR can be used. The parameter column can be overwritten in sub-queries.

Examples

In the examples below, the function get_posts() can be used instead of WP_Query. The arguments will be sent from get_posts() unchanged to WP_Query, and the results will be given back unchanged too.

Load all posts from October 30th 2012

1
2
3
4
5
6
$query_args = array(
'year'  => 2012,
'month' => 12,
'day'   => 30
);
$posts = get_posts( $query_args );

Load all posts of the current week

1
2
3
4
5
$query_args = array(
'w'    => date( 'W' ),
'year' => date( 'Y' ),
);
$posts = get_posts( $query_args );

Load all posts for the 47th and the 215th day of 2012

Hint: The 47. day of 2012 is February the 16th and the 215th day is August the 2nd. When will this be useful? I have no idea (at the moment). 🙂

1
2
3
4
5
6
7
8
9
$query_args = array(
'date_query' => array(
array (
'dayofyear' => array( 47, 215 ),
'compare' => 'IN',
)
)
);
$posts = get_posts( $query_args );

Load all post older than one year, but changed within the last month

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$query_args = array(
'date_query' => array(
array(
'column' => 'post_date_gmt',
'before' => '1 year ago',
),
array(
'column' => 'post_modified_gmt',
'after'  => '1 month ago',
),
'relation'  => 'AND',
)
);
$posts = get_posts( $query_args );

Load all post between September 1st 10 am and September 10th

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$before_args = array(
'day'    => 10,
'month'  => 9,
'year'   => gmdate( 'Y' ),
'hour'   => 12,
'minute' => 0,
);
 
$query_args = array(
'date_query' => array(
array(
'before' => $before_args,
'after'  => '10. September 2013',
),
'inklusive' => true,
)
);
$posts = get_posts( $query_args );

Extending the class WP_Comment_Query/ get_comments()

Unfortunately, the extension of the comments API is limited to the “Extended Search”. But we don’t have to use complex filters anymore to load the comments. A small example, comparing how it was done before WordPress 3.7 and how we can do it now:

Example

Load all comments, posted last week:

Before 3.7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* adding a date-filter to the comment_query to fetch comments from last week
*
* @param Array $clauses
* @return Array $clauses
*/
function marketpress_comments_last_week_filter( $clauses ){
$last_week = gmdate( 'W' ) - 1;
$sql = " (  AND " . _wp_mysql_week( 'comment_date' ) . " = %d ) ";
$clauses['where'] .= $wpdb->prepare( $sql, $last_week );
return $clauses;
}
add_filter('comments_clauses', 'marketpress_comments_last_week_filter' );
$comments = get_comments();
remove_filter( 'comments_clauses', 'marketpress_comments_last_week_filter' );

After 3.7

1
2
3
4
5
6
7
8
9
$last_week = gmdate( 'W' ) - 1;
$query_args = array(
'date_query' => array(
array(
'week' => $last_week
)
)
);
$comments = get_comments( $query_args );

Resume

With the new extension, we are able to create new SQL queries fast and easily. Also we don’t have to remember date formatting. Unfortunately, a similar implementation for the user API is missing. The date of registration user_registered could be handled by the WP_Date_Query too. To make it work, we would need to take a look at the class WP_User_Query with pre_user_query and additionally add the class WP_Date_Query to the date_query_valid_columns and add the column user_registered. Also the implementation in WP_Comment_Query is not complete, and the doubled parameters should be be handled better in the future. I hope this will happen until the next release.

Post Sharing

Author Avatar

Hi! My name is Chris and I enthusiastically develop the web from back-end to front-end at Inpsyde. When I'm not discussing accessibility, you can find me at Google+. In my spare time I study computer science, do a lot of sports, read one book after the other and blog at www.chrico.info.

Also Interesting

New Plugin: Slack Connector - Connect WordPress, WooCommerce and Slack

by Michael Firnkes

Initially we merely wanted to optimize our own Slack-processes. With automated notifications from our MarkettPress shop, the blog and our forums. The resul ...

Read more

Inpsyde is the first WordPress.com VIP partner in Germany

by Michael Firnkes

Automattic has given us the title of WordPress.com VIP Service Partner, the first in Germany, Austria and Switzerland. Worldwide, there are only eleven com ...

Read more

Inpsyde is a WooExperts Gold Partner of WooCommerce

by Michael Firnkes

A few weeks ago, Inpsyde GmbH with MarketPress became a certified WooExperts Gold Partner. This is confirmation from WooThemes of our agency’s know-h ...

Read more

BackWPup Pro: Secure WordPress Backup with Google Drive

by Michael Firnkes

The Pro version of our BackWPup plugin supports the backup of WordPress databases and files to Google Drive. But how do you set something like that up? And ...

Read more