WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Comparing timestamps in WP_Query (11 posts)

  1. bigmitch
    Member
    Posted 2 years ago #

    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).

  2. vtxyzzy
    Member
    Posted 2 years ago #

    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

  3. bigmitch
    Member
    Posted 2 years ago #

    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'?

  4. vtxyzzy
    Member
    Posted 2 years ago #

    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.

  5. bigmitch
    Member
    Posted 2 years ago #

    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').

  6. vtxyzzy
    Member
    Posted 2 years ago #

    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.

  7. bigmitch
    Member
    Posted 2 years ago #

    Sorry, but how do I find that info?

  8. vtxyzzy
    Member
    Posted 2 years ago #

    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.

  9. bigmitch
    Member
    Posted 2 years ago #

    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?

  10. vtxyzzy
    Member
    Posted 2 years ago #

    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);
  11. bigmitch
    Member
    Posted 2 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic