Meta compare with date (stored as string) not working

Question

Introducing `STR_TO_DATE`

MySQL has a STR_TO_DATE function that you could leverage for the scope.

To use it you need to filter the WHERE clause of a WP_Query, probably using posts_where filter.

That function allows to format a column value as a date by using a specific format.

By default, WordPress uses the CAST MySQL function that expects the value to be in the format Y-m-d H:i:s (according to PHP date arguments).

STR_TO_DATE, on the contrary, allows to pass a specific format. The format “placeholders” are not the same PHP uses, you can see supported MySQL format placeholders here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format.

Workflow

What I can suggest as workflow is:

create a WP_Query object
use posts_where to add a WHERE clause that makes use of STR_TO_DATE
remove the filter have the query run, to avoid affecting any other query

Note that STR_TO_DATE can be used also to order values. In case you want to do that, you would need to use posts_orderby filter.

The filtering class

Here there’s a class (PHP 7+) that wraps the workflow described above for ease re use:

class DateFieldQueryFilter {

    const COMPARE_TYPES = [ '<', '>', '=', '<=', '>=' ];
    private $date_key;
    private $date_value;
    private $format;
    private $compare="=";
    private $order_by_meta="";

    public function __construct(
        string $date_key,
        string $date_value,
        string $mysql_format,
        string $compare="="
    ) {
        $this->date_key   = $date_key;
        $this->date_value = $date_value;
        $this->format     = $mysql_format;
        in_array($compare, self::COMPARE_TYPES, TRUE) and $this->compare = $compare;
    }

    public function orderByMeta(string $direction = 'DESC'): DateFieldQueryFilter {
        if (in_array(strtoupper($direction), ['ASC', 'DESC'], TRUE)) {
            $this->order_by_meta = $direction;
        }
        return $this;
    }

    public function createWpQuery(array $args = []): \WP_Query {
        $args['meta_key'] = $this->date_key;
        $this->whereFilter('add');
        $this->order_by_meta and $this->orderByFilter('add');
        $query = new \WP_Query($args);
        $this->whereFilter('remove');
        $this->order_by_meta and $this->orderByFilter('remove');
        return $query;
    }

    private function whereFilter(string $action) {
        static $filter;
        if (! $filter && $action === 'add') {
            $filter = function ($where) {
                global $wpdb;
                $where and $where .= ' AND ';
                $sql = "STR_TO_DATE({$wpdb->postmeta}.meta_value, %s) ";
                $sql .= "{$this->compare} %s";
                return $where . $wpdb->prepare($sql, $this->format, $this->date_value);
            };
        }
        $action === 'add'
            ? add_filter('posts_where', $filter)
            : remove_filter('posts_where', $filter);
    }

    private function orderByFilter(string $action) {
        static $filter;
        if (! $filter && $action === 'add') {
            $filter = function () {
                global $wpdb;
                $sql = "STR_TO_DATE({$wpdb->postmeta}.meta_value, %s) ";
                $sql .= $this->order_by_meta;
                return $wpdb->prepare($sql, $this->format);
            };
        }
        $action === 'add'
            ? add_filter('posts_orderby', $filter)
            : remove_filter('posts_orderby', $filter);
    }
}

Sorry if this not include much comments or explaination, it is hard to write code here.

Where the “magic” happen

The “core” of the class is the filter that get added to posts_where before the query runs and removed after that. It is:

function ($where) {
    global $wpdb;
    $where and $where .= ' AND ';
    // something like: STR_TO_DATE(wp_postmeta.meta_value,'%b %e, %Y') < 'Feb 1, 2017'
    $sql = "STR_TO_DATE({$wpdb->postmeta}.meta_value, %s) {$this->compare} %s";

    return $where . $wpdb->prepare($sql, $this->format, $this->date_value);
};

You can see here how we are telling WordPress to query the posts. Note that
STR_TO_DATE is a computation that does not comes for free, and will be quite heavy (expecially for CPU) in case of many thousands (or millions) of rows.

How to make use of it

By the way, the class can then be used it like this:

$query_filter = new DateFieldQueryFilter (
    'expiration_date', // meta key
    date('M j, Y'),    // meta value
    '%b %e, %Y',       // date format using MySQL placeholders
    '<'                // comparison to use
);

$query = $query_filter->createWpQuery(['posts_per_page' => - 1]);

while($query->have_posts()) {
     $query->the_post();
     // rest of the loop here
}

In case you want to order by a standard WordPress field, e.g. post date, post title…, you could simple pass related order and orderby query arguments to the DateFieldQueryFilter::createWpQuery() method.

In case you want to order using the same meta value, the class provides the DateFieldQueryFilter::orderByMeta() for the scope.

E.g. in the last snippet you would create the query like this:

$query = $query_filter
             ->orderByMeta('DESC')
             ->createWpQuery(['posts_per_page' => - 1]);

Conclusion

Even if this works, when it is possible, dates should be saved in database using MySQL format or timestamps, because that simplify a lot any filtering or ordering operation.

In fact, it is much easier to convert the date in the desired format for output after they are retrieved from database.

Even if computation happen either way, doing it after it will be done only on the records returned, but letting MySQL perform the computation it needs to act on **all* the records, requiring much more work.

If you store the dates in MySQL format WordPress provides a function, mysql2date(), that can be used for to convert them to a desired format and it’s also locale aware.

Introducing STR_TO_DATE