WordPress.org

Ready to get started?Download WordPress

Forums

How Can We Query Dates In Post Meta? (16 posts)

  1. WebTechGlobal
    Member
    Posted 1 year ago #

    My query works when the date argument is removed, it returns 10 rows.

    When I add my AND line for comparing a date value I have stored in post meta too a PHP date with the same format. The query does not return any records.

    My conclusion is that it is because meta values are strings and this may need a date to be stored in a MySQL date data type.

    How do I get around this? Here is the query...

    `SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
    WHERE wposts.ID = metaprojectcode.post_id
    AND wposts.ID = metaupdated.post_id
    AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
    AND (metaprojectcode.meta_key = 'csv2post_last_update' AND metaupdated.meta_key < '2012-11-04 23:41:31')
    LIMIT 1'

    So you see I want to get posts that share one meta value and have a date value that is less than the example in the query. Should you be wondering, I do have a date of 2012-11-04 00:45:13 in meta which has the same post ID as a meta record with proh4hyq2.

    I know a way to do this that would require possibly very long PHP loops and 2 queries. So an alternative would help greatly and advice be appreciated.

  2. WebTechGlobal
    Member
    Posted 1 year ago #

    I just realized there may be a whole another approach I could be using. I forget about "post_modified" columns in the posts table. However that will not be suitable if post_modified is not updated when meta, categories etc are edited. I need to go check that.

    My date value indicates when CSV 2 POST makes a change to a post/page,a post as the users sees it.

    That is essentially what my csv2post_last_update value is, which is still relevant because it can be used to determine when CSV 2 POST changed the post record.

  3. dc5ala
    Member
    Posted 1 year ago #

    A small test on the postmeta table showed that a < or > comparison worked for meta_value at least. Here's an example of querying postmeta for The Events Calendar plugin:

    mysql> SELECT * FROM wp_postmeta WHERE meta_key='_EventStartDate' AND meta_value > '2012-12-03 20:00:00' AND meta_value < '2012-12-04 20:00:00';
    +---------+---------+-----------------+---------------------+
    | meta_id | post_id | meta_key        | meta_value          |
    +---------+---------+-----------------+---------------------+
    |  143208 |   40148 | _EventStartDate | 2012-12-04 00:00:00 |
    |  155597 |   40149 | _EventStartDate | 2012-12-04 13:00:00 |
    +---------+---------+-----------------+---------------------+
    2 rows in set (0.02 sec)

    Another way you could try is using STR_TO_DATE(), see MySQL date and time functions:

    mysql> SELECT * FROM wp_postmeta WHERE meta_key='_EventStartDate' AND STR_TO_DATE(meta_value, GET_FORMAT(DATETIME, 'ISO')) > '2012-12-03 20:00:00' AND STR_TO_DATE(meta_value, GET_FORMAT(DATETIME, 'ISO')) < '2012-12-04 20:00:00';
    +---------+---------+-----------------+---------------------+
    | meta_id | post_id | meta_key        | meta_value          |
    +---------+---------+-----------------+---------------------+
    |  143208 |   40148 | _EventStartDate | 2012-12-04 00:00:00 |
    |  155597 |   40149 | _EventStartDate | 2012-12-04 13:00:00 |
    +---------+---------+-----------------+---------------------+
    2 rows in set (0.01 sec)

    You get the same result set.

  4. WebTechGlobal
    Member
    Posted 1 year ago #

    Was nice to wake up to such a good answer this morning thanks.

    It make think about trying greater than on the same query and that returned a record. However it should not have so it must be a format issue.

    The meta value stored is 2012-11-04 00:45:14.

    Even in MySQL Workbench it is behaving as if 2012-11-04 00:45:14 is greater than 2012-11-04 23:41:31.

    Oh and there is a correct to my original query however before making this post it was correct. I had started over again in trying to figure this issue out and typed it up wrong.

    SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
        WHERE wposts.ID = metaprojectcode.post_id
        AND wposts.ID = metaupdated.post_id
        AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
        AND (metaupdated.meta_key = 'csv2post_last_update' AND metaupdated.meta_key > '2012-11-04 23:41:31')
        LIMIT 1

    I will try your second method.

  5. WebTechGlobal
    Member
    Posted 1 year ago #

    I tried a few approaches because I've not worked with functions like this in MySQL before but I think this is the proper syntax and it still does not return records.

    SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated WHERE wposts.ID = metaprojectcode.post_id AND wposts.ID = metaupdated.post_id AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2') AND (metaprojectcode.meta_key = 'csv2post_last_update' AND STR_TO_DATE(metaupdated.meta_key, GET_FORMAT(DATETIME, 'ISO')) < STR_TO_DATE('2012-11-05 12:35:04', GET_FORMAT(DATETIME, 'ISO'))) LIMIT 1'

    It has been a while since I spent so many hours on one problem, just like the Uni days lol

  6. dc5ala
    Member
    Posted 1 year ago #

    Is this part of your query correct?
    metaupdated.meta_key > '2012-11-04 23:41:31')

    You are checking for meta_key and not for meta_value there.

  7. WebTechGlobal
    Member
    Posted 1 year ago #

    This query works in MySQL Workbench but it does not work in PHP. This is the query after using var_dump and it return the exact number of records it should.

    This is a first for me. A query that works outside of PHP.

    SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
    WHERE wposts.ID = metaprojectcode.post_id
    AND wposts.ID = metaupdated.post_id
    AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
    AND (metaupdated.meta_key = 'csv2post_last_update')
    AND (metaupdated.meta_key < STR_TO_DATE('2012-11-05 12:43:39', GET_FORMAT(DATETIME, 'ISO')))'
  8. WebTechGlobal
    Member
    Posted 1 year ago #

    Sorry no the actual query and I just tried it again is ...

    SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
    WHERE wposts.ID = metaprojectcode.post_id
    AND wposts.ID = metaupdated.post_id
    AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
    AND (metaupdated.meta_key = 'csv2post_last_update')
    AND (metaupdated.meta_value < STR_TO_DATE('2012-11-05 12:43:39', GET_FORMAT(DATETIME, 'ISO')))
    LIMIT 1

    This returns a record in MySQL Workbench but not in PHP

  9. dc5ala
    Member
    Posted 1 year ago #

    You should post your php code with the query. And is there any reason for this double alias: wp_postmeta metaprojectcode, wp_postmeta metaupdated?

    I still suspect your last line is comparing the wrong column, since the meta_key is 'csv2post_last_update' i would expect a meta_value check against it.

  10. dc5ala
    Member
    Posted 1 year ago #

    About STR_TO_DATE(), you can use it on the column:

    AND (STR_TO_DATE(metaupdated.meta_value, GET_FORMAT(DATETIME, 'ISO')) < '2012-11-05 12:43:39')

    After my test i think it's not needed at all, though.

  11. WebTechGlobal
    Member
    Posted 1 year ago #

    And with STR_TO_DATE on the meta value, again does not return anything in PHP using WordPress get_results() but returns a record in MySQL Workbench...

    SELECT wposts.* FROM wp_posts wposts, wp_postmeta metaprojectcode, wp_postmeta metaupdated
    WHERE wposts.ID = metaprojectcode.post_id
    AND wposts.ID = metaupdated.post_id
    AND (metaprojectcode.meta_key = 'csv2post_project_code' AND metaprojectcode.meta_value = 'proh4hyq2')
    AND (metaupdated.meta_key = 'csv2post_last_update')
    AND (STR_TO_DATE(metaupdated.meta_value, GET_FORMAT(DATETIME, 'ISO')) < STR_TO_DATE('2012-11-05 12:43:39', GET_FORMAT(DATETIME, 'ISO')))
    LIMIT 1

    I can tell I'm going to need to go with the longer approach. Query the meta first and use LIMIT 1 then use the post_id from meta table to query the post. That is not a whole lot more processing.

  12. WebTechGlobal
    Member
    Posted 1 year ago #

    Yes sure. I did try it already, on both on each of them and trying the query with variable and with date string.

    Tried loads of variations. What you have told me allows it to work in MySQL Workbench only, which is so strange especially how you have it working!

    They query is going through get_results() and the returned value is being dumped, which has always been NULL with the queries that work in Workbench.

  13. WebTechGlobal
    Member
    Posted 1 year ago #

    Double alias is something I learned from more than one site for the first time in order to perform this.

    I need a post record that has two specific meta keys, one of which is a specific value and the other is a date.

    Multiple sites show this is possible but I'm in agreement with you when working in PHP or at least in WordPress this query does not work but it does work in the MySQL console which must be why sites state this approach as possible.

    Thank you for all your help.

  14. dc5ala
    Member
    Posted 1 year ago #

    When it works in the mysql console it should also work when PHP sends the query - both are just clients. Without having a look at the relevant code, i can't tell you why it does not work for you.

  15. WebTechGlobal
    Member
    Posted 1 year ago #

    Oh yes sorry about that.

    Not much to show...

    $pageposts = $wpdb->get_results($q, OBJECT);

    $pageposts gets dumped by var_dump() and global $wpdb is called before this. I have error display on and no MySQL errors. I have worked on this plugin for almost a year so I'm confident in how it stores the meta data plus its easy to check in database as each posts meta is grouped.

    The two meta values I'm searching in my query are close together and I can confirm there are 9 posts that share the same meta keys and I should get 9 records returned.

  16. dc5ala
    Member
    Posted 1 year ago #

    You should post a little more than just this line, especially how $q gets initialized. I promise i won't steal your code and sell it on the internet. ;)

Topic Closed

This topic has been closed to new replies.

About this Topic