Support

Account

Register Login

Forum Replies Created

  • Hi Andrea,

    So, the problem I had was that I made the mistake of thinking that my date and time picker custom field (showing time only) was linked/relative to the date and time picker custom field (displaying both date and time). They’re not linked, rather the time only picker value sets the time relative to the publish date of the post!

    So, here’s how my fields were set up:

    Date of event: date and time picker field (field name: day)

    Event time start: date and time picker field — time display only (field name: schedule_time_start)

    Event time finish: date and time picker field — time display only (field name: schedule_time_finish)

    So, for example, a post using these fields would be entered as:

    Date of event: 8/6/2016 12:00am
    Event time start: 9:00am
    Event time finish: 10:00am

    If I published this post on say, April 1, 2016 the ‘schedule_time_start’ was stored as 1459501200 which is the equivalent of April 1, 2016 9:00am GMT!

    That was the problem. What I should have done was ditched the event date field and just used the event time start & finish fields as date and time pickers.

    I had to debug this by querying the values that were being store in the wp_postmeta table in the database. This showed that the time values were set relative to the publish date.

    I hope this helps you.

  • Nevermind. I figured out the problem. Nothing to do with the query, but with the unix timestamps being saved to the database — the values were wrong, and that was causing the issue. Thanks for your efforts, John.

  • Hi John,

    I wasn’t able to get that filter working to log the queries in the error log, but I did do a print_r on the query array and got the MySQL query:

    SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (4,5,33,34,35,41)
) AND ( 
  wp_postmeta.meta_key = 'schedule_time_start' 
  AND 
  ( 
    ( mt1.meta_key = 'date' AND CAST(mt1.meta_value AS CHAR) LIKE '%1470268800%' ) 
    OR 
    ( mt1.meta_key = 'day' AND CAST(mt1.meta_value AS CHAR) LIKE '%1470268800%' )
  )
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC

    See anything unusual?

  • Hi John, Thanks for replying. The value is stored as a Unix timestamp (seconds since the Unix epoch). So 9am GMT on August 6, 2016 is 1459242000.

    I still don’t understand why the WordPress publish date is factoring into this and affecting the sort. Any thoughts?

  • *bump*

    Any thoughts from the community would be appreciated!

  • Hi John,

    Thanks for your reply.

    schedule_time_start is a “Date and Time Picker” field. It is ordering everything correctly by this field BUT only for posts published/updated on the same date. It is as if they are being grouped by the standard WordPress publish date and then sorted by schedule_time_start within those groups.

    My questions is: why is this happening and how can I modify my query to return all posts into one group, instead of multiple?

    Thanks!

  • You are correct, John. That was it. Thanks so much.

  • Thanks, John. That document was helpful.

    For someone else who may stumble across this article and find my query helpful, here it is:

    	$my_query = new WP_Query(array(
        'category_name' => 'artist',
        'posts_per_page' => -1,
        'meta_query' => array(
            'relation' => 'AND',
            'gender_clause' => array(
                'key' => 'gender',
                'value' => 'f',
            ),
            'sort_clause' => array(
                'key' => 'sort_name',
                'compare' => 'EXISTS',
            ),
        ),
        'orderby' => 'sort_clause',
        'order' => 'ASC',
    ));

    Basically, I am querying posts of the ‘artist’ category, and then selecting those posts where the ‘gender’ (a custom field I have setup) is set to the value of ‘f’. There is another custom field called ‘sort_name’ that I use to order the posts by.

  • This reply has been marked as private.

  • Excellent. Thanks for the explanation, John.

  • Thanks for you reply, John. I will have to give this a try, though I can already see how this could potentially not tie the original field to the field created via duplication in the layout — if I were to modify the original field, I doubt that modification would carry through to the duplicated field based on your answer.

Viewing 11 posts - 1 through 11 (of 11 total)