Class: WP_Date_Query

Class for generating SQL clauses that filter a primary query according to date.

WP_Date_Query is a helper that allows primary query classes, such as WP_Query, to filter their results by date columns, by generating WHERE subclauses to be attached to the primary SQL query string.

Attempting to filter by an invalid date value (eg month=13) will generate SQL that will return no results. In these cases, a _doing_it_wrong() error notice is also thrown. See WP_Date_Query::validate_date_values().


Properties

Name Type(s) Default Value Summary
queries array Array of date queries.

See WP_Date_Query::__construct() for information on date query arguments.

relation string The default relation between top-level queries. Can be either 'AND' or 'OR'.
column string The column to query against. Can be changed via the query arguments.
compare array The value comparison operator. Can be changed via the query arguments.
time_keys array Supported time-related parameter keys.

Methods

WP_Date_Query:: __construct( array $date_query, array $default_column = 'post_date' )

Constructor. Since 3.7.0. Since 4.0.0. Since 4.1.0.

Time-related parameters that normally require integer values ('year', 'month', 'week', 'dayofyear', 'day', 'dayofweek', 'dayofweek_iso', 'hour', 'minute', 'second') accept arrays of integers for some values of 'compare'. When 'compare' is 'IN' or 'NOT IN', arrays are accepted; when 'compare' is 'BETWEEN' or 'NOT BETWEEN', arrays of two valid values are required. See individual argument descriptions for accepted values.

Arguments

Name Type(s) Default Value Description
$date_query array

{ Array of date query clauses.

@type array {
    @type string $column   Optional. The column to query against. If undefined, inherits the value of
                           the `$default_column` parameter. Accepts 'post_date', 'post_date_gmt',
                           'post_modified','post_modified_gmt', 'comment_date', 'comment_date_gmt'.
                           Default 'post_date'.
    @type string $compare  Optional. The comparison operator. Accepts '=', '!=', '>', '>=', '<', '<=',
                           'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. Default '='.
    @type string $relation Optional. The boolean relationship between the date queries. Accepts 'OR' or 'AND'.
                           Default 'OR'.
    @type array {
        Optional. An array of first-order clause parameters, or another fully-formed date query.

        @type string|array $before {
            Optional. Date to retrieve posts before. Accepts `strtotime()`-compatible string,
            or array of 'year', 'month', 'day' values.

            @type string $year  The four-digit year. Default empty. Accepts any four-digit year.
            @type string $month Optional when passing array.The month of the year.
                                Default (string:empty)|(array:1). Accepts numbers 1-12.
            @type string $day   Optional when passing array.The day of the month.
                                Default (string:empty)|(array:1). Accepts numbers 1-31.
        }
        @type string|array $after {
            Optional. Date to retrieve posts after. Accepts `strtotime()`-compatible string,
            or array of 'year', 'month', 'day' values.

            @type string $year  The four-digit year. Accepts any four-digit year. Default empty.
            @type string $month Optional when passing array. The month of the year. Accepts numbers 1-12.
                                Default (string:empty)|(array:12).
            @type string $day   Optional when passing array.The day of the month. Accepts numbers 1-31.
                                Default (string:empty)|(array:last day of month).
        }
        @type string       $column        Optional. Used to add a clause comparing a column other than the
                                          column specified in the top-level `$column` parameter. Accepts
                                          'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt',
                                          'comment_date', 'comment_date_gmt'. Default is the value of
                                          top-level `$column`.
        @type string       $compare       Optional. The comparison operator. Accepts '=', '!=', '>', '>=',
                                          '<', '<=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'. 'IN',
                                          'NOT IN', 'BETWEEN', and 'NOT BETWEEN'. Comparisons support
                                          arrays in some time-related parameters. Default '='.
        @type bool         $inclusive     Optional. Include results from dates specified in 'before' or
                                          'after'. Default false.
        @type int|array    $year          Optional. The four-digit year number. Accepts any four-digit year
                                          or an array of years if `$compare` supports it. Default empty.
        @type int|array    $month         Optional. The two-digit month number. Accepts numbers 1-12 or an
                                          array of valid numbers if `$compare` supports it. Default empty.
        @type int|array    $week          Optional. The week number of the year. Accepts numbers 0-53 or an
                                          array of valid numbers if `$compare` supports it. Default empty.
        @type int|array    $dayofyear     Optional. The day number of the year. Accepts numbers 1-366 or an
                                          array of valid numbers if `$compare` supports it.
        @type int|array    $day           Optional. The day of the month. Accepts numbers 1-31 or an array
                                          of valid numbers if `$compare` supports it. Default empty.
        @type int|array    $dayofweek     Optional. The day number of the week. Accepts numbers 1-7 (1 is
                                          Sunday) or an array of valid numbers if `$compare` supports it.
                                          Default empty.
        @type int|array    $dayofweek_iso Optional. The day number of the week (ISO). Accepts numbers 1-7
                                          (1 is Monday) or an array of valid numbers if `$compare` supports it.
                                          Default empty.
        @type int|array    $hour          Optional. The hour of the day. Accepts numbers 0-23 or an array
                                          of valid numbers if `$compare` supports it. Default empty.
        @type int|array    $minute        Optional. The minute of the hour. Accepts numbers 0-60 or an array
                                          of valid numbers if `$compare` supports it. Default empty.
        @type int|array    $second        Optional. The second of the minute. Accepts numbers 0-60 or an
                                          array of valid numbers if `$compare` supports it. Default empty.
    }
}

}

$default_column array

Optional. Default column to query against. Default 'post_date'. Accepts 'post_date', 'post_date_gmt', 'post_modified', 'post_modified_gmt', 'comment_date', 'comment_date_gmt'.


WP_Date_Query:: build_mysql_datetime( string | array $datetime, boolean $default_to_max = false )

Builds a MySQL format date/time based on some query parameters. Since 3.7.0.

You can pass an array of values (year, month, etc.) with missing parameter values being defaulted to either the maximum or minimum values (controlled by the $default_to parameter). Alternatively you can pass a string that will be run through strtotime().

Arguments

Name Type(s) Default Value Description
$datetime string | array

An array of parameters or a strotime() string

$default_to_max boolean

Whether to round up incomplete dates. Supported by values of $datetime that are arrays, or string values that are a subset of MySQL date format ('Y', 'Y-m', 'Y-m-d', 'Y-m-d H:i'). Default: false.

Returns

string | false

A MySQL format date/time or false on failure


WP_Date_Query:: build_time_query( string $column, string $compare, integer | null $hour = null, integer | null $minute = null, integer | null $second = null )

Builds a query string for comparing time values (hour, minute, second). Since 3.7.0.

If just hour, minute, or second is set than a normal comparison will be done. However if multiple values are passed, a pseudo-decimal time will be created in order to be able to accurately compare against.

Arguments

Name Type(s) Default Value Description
$column string

The column to query against. Needs to be pre-validated!

$compare string

The comparison operator. Needs to be pre-validated!

$hour integer | null

Optional. An hour value (0-23).

$minute integer | null

Optional. A minute value (0-59).

$second integer | null

Optional. A second value (0-59).

Returns

string | false

A query part or false on failure.


WP_Date_Query:: build_value( string $compare, string | array $value )

Builds and validates a value string based on the comparison operator. Since 3.7.0.

Arguments

Name Type(s) Default Value Description
$compare string

The compare operator to use

$value string | array

The value

Returns

string | false | integer

The value to be used in SQL or false on error.


WP_Date_Query:: get_compare( array $query )

Determines and validates what comparison operator to use. Since 3.7.0.

Arguments

Name Type(s) Default Value Description
$query array

A date query or a date subquery.

Returns

string

The comparison operator.


WP_Date_Query:: get_sql( )

Generate WHERE clause to be appended to a main query. Since 3.7.0.

Returns

string

MySQL WHERE clause.


WP_Date_Query:: get_sql_clauses( )

Generate SQL clauses to be appended to a main query. Since 4.1.0.

Called by the public WP_Date_Query::get_sql(), this method is abstracted out to maintain parity with the other Query classes.

Returns

array

{ Array containing JOIN and WHERE SQL clauses to append to the main query.

@type string $join  SQL fragment to append to the main JOIN clause.
@type string $where SQL fragment to append to the main WHERE clause.

}


WP_Date_Query:: get_sql_for_clause( array $query, array $parent_query )

Turns a first-order date query into SQL for a WHERE clause. Since 4.1.0.

Arguments

Name Type(s) Default Value Description
$query array

Date query clause.

$parent_query array

Parent query of the current date query.

Returns

array

{ Array containing JOIN and WHERE SQL clauses to append to the main query.

@type string $join  SQL fragment to append to the main JOIN clause.
@type string $where SQL fragment to append to the main WHERE clause.

}


WP_Date_Query:: get_sql_for_query( array $query, integer $depth )

Generate SQL clauses for a single query array. Since 4.1.0.

If nested subqueries are found, this method recurses the tree to produce the properly nested SQL.

Arguments

Name Type(s) Default Value Description
$query array

Query to parse.

$depth integer

Optional. Number of tree levels deep we currently are. Used to calculate indentation. Default 0.

Returns

array

{ Array containing JOIN and WHERE SQL clauses to append to a single query array.

@type string $join  SQL fragment to append to the main JOIN clause.
@type string $where SQL fragment to append to the main WHERE clause.

}


WP_Date_Query:: get_sql_for_subquery( array $query )

Turns a single date clause into pieces for a WHERE clause. Since 3.7.0.

A wrapper for get_sql_for_clause(), included here for backward compatibility while retaining the naming convention across Query classes.

Arguments

Name Type(s) Default Value Description
$query array

Date query arguments.

Returns

array

{ Array containing JOIN and WHERE SQL clauses to append to the main query.

@type string $join  SQL fragment to append to the main JOIN clause.
@type string $where SQL fragment to append to the main WHERE clause.

}


WP_Date_Query:: is_first_order_clause( array $query )

Determine whether this is a first-order clause. Since 4.1.0.

Checks to see if the current clause has any time-related keys. If so, it's first-order.

Arguments

Name Type(s) Default Value Description
$query array

Query clause.

Returns

boolean

True if this is a first-order clause.


WP_Date_Query:: sanitize_query( array $queries, array $parent_query = null )

Recursive-friendly query sanitizer. Since 4.1.0.

Ensures that each query-level clause has a 'relation' key, and that each first-order clause contains all the necessary keys from $defaults.

Arguments

Name Type(s) Default Value Description
$queries array
$parent_query array

Returns

array

Sanitized queries.


WP_Date_Query:: validate_column( string $column )

Validates a column name parameter. Since 3.7.0.

Column names without a table prefix (like 'post_date') are checked against a whitelist of known tables, and then, if found, have a table prefix (such as 'wp_posts.') prepended. Prefixed column names (such as 'wp_posts.post_date') bypass this whitelist check, and are only sanitized to remove illegal characters.

Arguments

Name Type(s) Default Value Description
$column string

The user-supplied column name.

Returns

string

A validated column name value.


WP_Date_Query:: validate_date_values( array $date_query = array() )

Validates the given date_query values and triggers errors if something is not valid. Since 4.1.0.

Note that date queries with invalid date ranges are allowed to continue (though of course no items will be found for impossible dates). This method only generates debug notices for these cases.

Arguments

Name Type(s) Default Value Description
$date_query array

The date_query array.

Returns

boolean

True if all values in the query are valid, false if one or more fail.


WordPress Developer Newsletter

Stay on top of the latest WordPress API changes, developer tool updates, security alerts and more.