4

I have a custom post type that has a number of meta boxes in it. One of these is an expiration_date field that stores its info in the db as a string formatted like

Feb 1, 2017

I am trying to write a query that will filter out those items that have a date in the past, only showing future ones. I have tried several versions of the following query, and none work:

$myquery = array (
'post_type' => $cpt,
    'numberposts'=>-1,
    'meta_key' => 'expiration_date',
    'meta_value' => date('M j, Y'),
    'meta_compare' => '<'
    ),
 );

The query returns all posts regardless of date, or zero posts if I reverse the carot (<)

I know that that in order to use date fields WP expects it to be in a different format (YYYY-MM-DD) but this is not how this app was built (by someone else), so the format above is what I am working with.

So the question is: Is there a way to convert the datetime BEFORE the compare? If I pull the field out by itself in any record, I can convert it (using strtotime), but of course I am trying to filter out values based on date before display so I have the right number in the set to work with at the start.

  • Does this help? wordpress.stackexchange.com/a/169006/110572 – Fayaz Feb 10 '17 at 2:32
  • no, because the format of the date is not a standard MySQL one, see above. But thanks. – Stephen Feb 10 '17 at 3:11
  • 5
    Unless something has changed in recent versions that I'm not aware of, you can only compare dates in YYYY-MM-DD format. I don't really see a practical reason to store it in any other format anyway. – Milo Feb 10 '17 at 4:24
  • It's certainly possible to do with raw SQL, but you would have to write the SQL query yourself to make it happen. It's also way more computationally expensive, as otherwise dates in descending units are just simple number comparisons without the query engine having to parse what a date is. – Milo Feb 10 '17 at 14:24
6
+50

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.

  • now that I have changed my format to use YYYY-MM-DD in the db, I can't test this (easily), but this definitely seems like the solution I was looking to have/understand if it was possible, so thanks and you have the bounty. – Stephen Feb 15 '17 at 14:56
  • Solid answer here. Would second that regarding dates, always store them YYYY-MM-DD format. Strtotime would also work if you use a consistent start point for comparison (Eg: 1/1/2000. I remember an issue like this stumping me for days on a project a while back. – Hybrid Web Dev Feb 15 '17 at 19:13
1

I ended up working around the problem of the initial date format by changing the expiration_date field format to YYYY-MM-DD (and then modifying the display of that field elsewhere so that it remains in the original format when end-users view it). So now a normal meta_query using DATETIME will work:

$myquery = array (
'post_type' => $cpt,
    'numberposts'=>-1,
    'meta_key' => 'expiration_date',
    'type'=> 'DATETIME',
    'meta_value' => date("Y-m-d"),
    'meta_compare' => '<'
    ),
 );

I would still like an answer to the above if one exists, where the value can be string converted on the fly before compare, so I guess I will hold off on accepting my own answer for a bit hoping for that answer.

  • This is the smart solution. – prosti Feb 16 '17 at 4:28
1

Did you try using meta_query in WP_Query?

$args = array(
    'post_type'  => $cpt,
    'meta_query' => array(
        array(
            'key'     => 'expiration_date',
            'value'   => date('M j, Y'),
            'compare' => '<',
        ),
    ),
);
$query = new WP_Query( $args );

also, make sure that the date in the postmeta table is of the same format that you pass as value in meta_query.

0

You can do date_parse_from_format to convert the date format:

date_parse_from_format('M j, Y', 'Feb 1, 2017');

Then you could reform the array as date("Y-m-d").

The complete array structure is:

[year] =>  
[month] => 
[day] =>  
[hour] =>  
[minute] => 
[second] =>
[fraction] =>  
[warning_count] =>  
[warnings] => Array() 
[error_count] => 
[errors] => Array() 
[is_localtime] => 
[zone_type] => 
[zone] => 
[is_dst] =>
  • so are you saying that in my query args I would use that line around the expiration_date key value? Or somewhere else? – Stephen Feb 11 '17 at 10:11
  • Yes. Then you can put the information as date("Y-m-d") or other form. – Mudy S Feb 13 '17 at 14:44
  • Actually, I don't see how this can work given my query above. Can you provide an example using my query above? It will not work around the key because that is just the name of the key, and it is correct. – Stephen Feb 13 '17 at 15:30
0

Maybe you could try something like this?

$args2 = array( 
            'post_type'     => $cpt,
            'posts_per_page'=> -1, 
            'meta-key' => 'expiration_date',
            'meta_query' => Array ( 
                                Array ('key' => 'expiration_date', 
                                        'compare' => '>=', 
                                        'value' => date('M j, Y',strtotime('now')), //strtotime for value for now, date() to format the timestamp
                                            'type' => 'DATETIME'
                                            ) 
                                            ),
            'orderby'       =>   'meta_value', 
            'order'         =>   'ASC' //or DESC
            );

This works for me in getting only events, which are still in the future.

EDIT: I have to apologize, this does NOT work. I was driving to an appointment, when i realized where the sorting was actually done. Way down my file was an if-statement left from my previous try, which did the sorting and ordering. Removed that line and it stopped working ...

0

The smart solution would be to use the expiration_date field format to YYYY-MM-DD.

This way you will be able to create future WordPress meta queries without the problem.

The conversion should be a breeze.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.