WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] Comparing timestamps in WP_Query

[Resolved] Comparing timestamps in WP_Query

  • I can’t figure out how to compare timestamps in the WP_query. On the site, classes/events are entered as posts with meta added for the date they begin. In ‘calendar_functions.php’, $begtime is defined as the timestamp for the date the class/event starts (not the date the post is created) and $today is defined as today’s timestamp. I’ve tested both of those and they do return correct values.

    The below code works fine, it displays a random class/event… except that past classes/events show up. I want to be able to only display items that happen from today on. Its not an option to go in and delete classes/events that have already happened.

    <?php include('calendar_functions.php'); ?>
    <?php $my_query = new WP_Query('"$begtime >= $today"&posts_per_page=1&cat=5,43&orderby=rand');
    if (have_posts()) : while ($my_query->have_posts()) : $my_query->the_post();
    $do_not_duplicate = $post->ID;
    ?>

    I will also need to be able to compare two sets of dates, as some classes have two sessions, ex. “$begtime >= $today -or- $begtime2 >= $today”. ($begtime2 is also already defined).

Viewing 10 replies - 1 through 10 (of 10 total)
  • If the values are stored in the postmeta table, you need to use a ‘custom field’ type query. Assuming the meta_key for the beginning time is ‘begtime’ and the dates are stored there in the Y-m-d format, and the variable $today contains a date in the same format, your query would look like this:

    $args = array(
       'posts_per_page' => 1,
       'cat' => 5,
       'orderby' => 'rand',
       'meta_key' => 'begtime',
       'meta_value' => $today,
       'meta_compare' => '>=',
    );
    $my_query = new WP_Query($args);

    For the case with two dates, you will need to use a meta_query as described in the Codex here:

    http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

    I’m still having a problem as $begtime is not meta from the post, its a defined variable… All posts are originally entered into a Filemaker DB which then writes to the WordPress DB. The date meta in the WP DB is in j-m-Y format, not good for comparing. $begtime just redefines the date meta into Y-m-d format, so it can be used for comparison.

    So how would I compare the two variables (also keeping the posts_per_page, etc.)? Or can a variable be used in ‘meta_key’?

    Sorry, I don’t quite understand. How exactly is the date stored in the database? What is the table name, the field name, the variable type and the data format? How is the table related to wp_posts?

    I suspect that what you want can be done with a filter on the query, but I can’t be sure without knowing the details.

    The Filemaker DB writes the entry’s data to the WP DB. After that, when you go into the WP dashboard, the entry is present as a post.

    The post’s title, body copy, catagories and meta (begin/end dates (in MM-DD-YYYY format), prices, times, instructors, etc.) all come from Filemaker. The meta populates Flutter write panels.

    The two variables I’d need to compare are (in addition to the posts_per_page, etc.):
    (1) today’s date
    (2) entry’s beginning date (which would come from the entry’s meta, just redefined to match today’s date format).

    So it would be comparing two variables -or- “assigning” the entry’s beginning date variable as the ‘meta_key’ to compare against today’s date (‘meta_value’).

    I think I understand what you want to do, but cannot suggest a solution without the detail that I requested. I need to know the table name, field name, field type, data format, and how to link to the posts table in order to write the sql for the query.

    Sorry, but how do I find that info?

    You say that when you go to the Dashboard the data is in a Post. Are the beginning dates shown as Custom Fields below the Post? If so, what are the Custom Fields’ Names?

    And, please show a sample of one of the date values.

    Yes, after the event info is entered in Filemaker, it shows up as a post in WP. All event details (dates, prices, instructors, etc.) show up under the post in Flutter write panels. The Beginning Date field name is ‘class-begdate’.

    I did get into the WP DB, and found this for something that was just added, it looks like it actually is storing the date as YYYY-MM-DD:
    INSERT INTO accr_postmeta VALUES (68130, 3362, ‘class_begdate’, ‘2012-01-22’);

    So maybe it can be what you posted earlier but use ‘class_begdate’ as the meta_key?

    Using ‘class_begdate’ as the meta_key sounds right. You will need to set $today to the correct day before using the code I posted.

    $today = date('Y-m-d',time());
    $args = array(
       'posts_per_page' => 1,
       'cat' => 5,
       'orderby' => 'rand',
       'meta_key' => 'class_begdate',
       'meta_value' => $today,
       'meta_compare' => '>=',
    );
    $my_query = new WP_Query($args);

    As Peter Griffin would say… Freakin’ Sweet!. Thanks, that seems to work.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘[Resolved] Comparing timestamps in WP_Query’ is closed to new replies.