Archive Listings Filtered by Date Values in a Custom Field/Post Meta?

The question:

(Moderator’s Note: The original title was “using archive by date with a custom date”)

I’m adding an additional date to posts as a custom field. Now I want the archive to show posts by the custom date and not by the published date. For example
I’ve got a post published today (22nd December) but the custom date is set to 1st January. When I load the URL archives/date/2011/01 in the browser the posts with the custom date that match are not displayed (obviously).

Is there a way to modify the behavior of page generated for the archives/date/2011/01 URL in order to retrive the posts by the custom date?


The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

As with many things in WordPress there are several ways to do what you want. I’m going to explain one of them.

Remove the 'year', 'monthnum' and 'day' Query Variables

You can modify the parameters to the query WordPress uses on the archive URLs inside the 'pre_get_posts' hook. Those parameters are captured as an associative array by the rewrite rules into the property of the WP_Query object called query_vars variables 'year', 'monthnum' and 'day'.

When $wp_query->query_vars has values for 'year', 'monthnum' and 'day' WordPress will use them when querying the list of posts so the first step is to remove those key-value pairs from the query array using PHP’s unset() function (note that $wp_query is a global variable in WordPress that contains the current query object as an instance of the WP_Query class.)

The yoursite_pre_get_posts() function below triggered by the 'pre_get_posts' hook will remove those variables from the query_vars array but only when the query is the main query *(i.e. the $query passed to the hook is exactly equal to the global $wp_the_query or $query===$wp_the_query) and only when the query is an archive query. If you add this to your theme’s functions.php file you’ll find your archive pages display all posts, not just the ones for the date (so we are halfway to what you want…):

add_action('pre_get_posts', 'yoursite_pre_get_posts' );
function yoursite_pre_get_posts( $query ) {
  global $wp_the_query;
  if ($query===$wp_the_query && $query->is_archive) {

Add Query Variables for Your Custom Field

Now is where it gets a little tricky. In v3.0.x WordPress only allows you to query for custom field using the values in the query_var array and it doesn’t allow us to apply functions to those criteria either.

The following modification to yoursite_pre_get_posts() would allow your archive query to match an exact date assuming you stored the date in 'YYYY-MM-DD' format in the custom field:

add_action('pre_get_posts', 'yoursite_pre_get_posts' );
function yoursite_pre_get_posts( $query ) {
  global $wp_the_query;
  if ($query===$wp_the_query && $query->is_archive) {
    $qv = &$query->query_vars;
    $date = mktime(0,0,0,$qv['monthnum'],$qv['day'],$qv['year']);
    $qv['meta_key'] = 'Custom Date';
    $qv['meta_value'] = date("Y-m-d",$date);

And here’s what that looks like in a screenshot:

Of course that’s not really want you want. You want to query for year & month instead of just the specific day, or even for just the year. As I said before, that’s where it gets tricky.

Use the 'posts_where' Hook to Modify the SQL WHERE Clause

Basically you need to go deeper into hooks and and call the 'posts_where' hook, which I always try to avoid if possible since when you are doing text find and replace on SQL query fragments it’s very easy to write code that will be incompatible with another plugin that modifies the same SQL query. Nonetheless, if you need it and nothing else will work then it’s there for you as a last resort.

So the yoursite_posts_where() function searches the WHERE clause that WordPress builds for the text "AND wp_postmeta.meta_value = '2011-01'" and replaces it with the text "AND SUBSTRING(wp_postmeta.meta_value,1,7) = '2011-01'", and here’s the code:

function yoursite_posts_where( $where, $query ) {
  global $wp_the_query;
  if ($query===$wp_the_query && $query->is_archive) {
    $meta_value = $query->query_vars['meta_value'];
    $length = strlen($meta_value);
    $find = "AND wp_postmeta.meta_value = '{$meta_value}'";
    $replace = "AND SUBSTR(wp_postmeta.meta_value,1,{$length}) = '{$meta_value}'";
    $where = str_replace($find,$replace,$where);
  return $where;

Modify yoursite_pre_get_posts() to support multiple Year-Month-Day Options

Of course that will require we modify our yoursite_pre_get_posts() function to support different length meta values: 'YYYY', 'YYYY-MM' and 'YYYY-MM-DD' (Note the use of what appears to be complex use of PHP’s mktime() and date() functions are merely to avoid having to worry about these patterns: 'YYYY-M', 'YYYY-MM-D' and 'YYYY-M-D'):

add_action('pre_get_posts','yoursite_pre_get_posts' );
function yoursite_pre_get_posts( $query ) {
  global $wp_the_query;
  if ($query===$wp_the_query && $query->is_archive) {
    $qv = &$query->query_vars;  // Make it less tedious to reference
    if ($qv['monthnum']==0) // Just search on YYYY
      $date = $qv['year'];
    else if ($qv['day']==0) // Search on YYYY-MM
      $date = date('Y-m',mktime(0,0,0,$qv['monthnum'],1,$qv['year']));
      $date = date('Y-m-d',mktime(0,0,0,$qv['monthnum'],$qv['day'],$qv['year']));
    $qv['meta_key'] = 'Custom Date';
    $qv['meta_value'] = $date;

And that gives us the following which pretty much wraps up what you were looking for:

Code to Display the Custom Date in the Theme

BTW, here’s the code I used in the theme to grab the Custom Date meta value:

<?php if ($custom_date = get_post_meta($post->ID,'Custom Date',true)): ?>
  <h2>Custom Date: <?php echo $custom_date; ?></h2>
<?php endif; ?>

P.S. Remember the User Must Enter in 'YYYY-MM-DD' format

Remember the user needs to enter the Custom Date in the 'YYYY-MM-DD' format for this to work. Of course you can make it friendlier to the user via several different methods, i.e. in the SQL code or via a 'save_post' hook that reformats the date but I’ll leave that as an exercise for someone else.

P.P.S. Option: Save Multiple Meta Values Instead

Another approach would have been to use the 'save_post' hook to save three (3) hidden meta values in the right format for matching to 'YYYY-MM-DD', 'YYYY-MM' and 'YYYY' maybe with the meta keys '_custom_date', '_custom_date_year_month' '_custom_date_year' which would be more resilient to other plugins that might also want to modify the SQL WHERE clause but would add three (3) additional hidden custom fields for every post so that might not be a good idea for a blog with a large number of posts and it is possible they could get out of sync somehow.

Method 2

Try this:

  • copy your archive.php (index.php if you don’t have archive) template to date.php
  • in date.php template use query_posts() to override sort, you will need to use

PS hm, I think date archives are usually like /year/month/, what are you using to get /archives/date/year/month/ ?..

PPS actually after looking at Mike’s answer this is likely not enough to affect selection of posts. Scratch this.

Method 3

Yes – you’ll need to either use query_posts() or get_posts() or write a custom query yourself.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment