Filtering / Querying Posts based on year (field stored yyyymmdd)

• alexpatin

• March 8, 2017 at 8:33 am

I have a post type called shows with a field called show_date, storing the date like yyyymmdd in the database. Is it possible to query / filter posts by the first four characters of the show_date string (i.e. the year)?

Ultimately, I want to be able to use the REST API to query / filter with this specific meta, but I am stuck figuring out how to do this. Here’s what I’ve been able to put together so far – this is my custom post type archive, which initially should pull all of the posts, but as I said, eventually I’d like to filter/query by year without having to have a separate year field or something.

<select id="years" name="yearFilter">
  <option>Select Year</option>
</select>

<?php 
  $posts_per_page = -1;
  // set up our archive arguments

  $archive_args = array (
    'post_type' => 'setlist-shows',
    'posts_per_page' => $posts_per_page,
    'meta_key'  => 'show_date',
    //'orderby' => 'meta_value_num',
    //'order'   => 'DESC'
  );

  $archive_query = new WP_Query( $archive_args );

?>

<div class="all-shows">
  <table>
    <tr>
      <th>Date</th>
      <th>Location</th>
      <th>Venue</th>
      <th></th>
    </tr>
    <?php while ( $archive_query->have_posts() ) : $archive_query->the_post(); 

      $date = get_field('show_date', false, false);
      $date = new DateTime($date);?>

    <tr>
      <td><?php echo $date->format('M j, Y');?></td>
      <td><?php the_field('location');?></td>
      <td><?php the_field('venue');?>
      <td><a href="<?php the_permalink();?>">Setlist</a></td>
    </tr>
  <?php endwhile; // end the custom loop ?>
  </table>
</div>
<?php wp_reset_postdata(); // always reset post data after a custom query ?>

</main>

<script>
jQuery(document).ready(function($){
  $.get("/wp-json/wp/v2/setlist-shows", function (posts) {
    $.each(posts, function(index, post) {
      showDate = post.show_date;
      showYear = showDate.substring(0,4);
      var years = '<option value="' + showYear + '">' + showYear + '</option>';     
      $('#years').append(years);
    });
  });
});
</script>

• John Huebner

• March 8, 2017 at 9:57 am

This is the only way I can think to do it

  $archive_args = array (
    'post_type' => 'setlist-shows',
    'posts_per_page' => $posts_per_page,
    'meta_key'  => 'show_date',
    'orderby' => 'meta_value_num',
    'order'   => 'DESC',
    'meta_query => array(
      array(
        'key' => show_date,
        'value' => '20170000',
        'compare' => '>'
      ),
      array(
        'key' => show_date,
        'value' => '20180000',
        'compare' => '<'
      )
    )
  );

• alexpatin

• March 8, 2017 at 10:13 am

It’s so obvious, that makes perfect sense haha. Clearly I have been working on this specific template for too long – thanks John!