Meta Query Filter By Date

Hi @ajwe, thank you for your feedback. To use a date string value, you have to choose the data type as date . Have you tried that?

Phi.

Hi Phi, I do have the data type set as Date. I don’t have a convenient place to upload a screen shot, but the fields are set as: Data Type: Date, Compare Operator: >=, Date Format: (blank, but I’ve also tried explicitly setting it to Y-m-d), Field Key: end_date (my custom field name, which is stored in the Y-m-d format), and Field Value: today (I’ve also tried “now” & various other options; it only works if I explicitly set it to a value like 2023-12-26).

If I set the Field Value manually to 2023-12-26, immediately in the block editor the two matching posts show up (and show up if I save the page & view it in a separate browser). If I set the Field Value to “today” (with or without the quotes), the posts disappear.

I’m using WordPress 6.4.2 and the php version is 8.1.2-1ubuntu2.14.

Hi @ajwe,

The plugin uses the strtotime function to convert a date string to a timestamp value, and then convert it back to a string value with the date function if the format is not timestamp. Could you test it in your site environment to see if the meta value is correct? Here is the code snippet:

date('Y-m-d', strtotime('today'));

Alternatively, could you try to input timestamp as the date format, if your custom field value is stored in a timestamp format.

Phi.

• This reply was modified 11 months, 1 week ago by Phi Phan.

Yeah, I was looking through the part of the plugin code that handles the meta query to see if I could figure out what I might be doing wrong and it all made sense (though I am no php expert). I ran php in interactive mode and it does the right thing:

php > echo date('Y-m-d', strtotime('today'));
2023-12-27

But after viewing the web page in the browser, I see the following error appear in the apache error.log (this is just a snippet):

WordPress database error Incorrect DATE value: 'today' for query \n\t\t\t\t\tSELECT SQL_CALC_FOUND_ROWS  awp_posts.ID\n\t\t\t\t\tFROM awp_posts  INNER JOIN awp_postmeta ON ( awp_posts.ID = awp_postmeta.post_id )\n\t\t\t\t\tWHERE 1=1  AND ( \n  ( awp_postmeta.meta_key = 'end_date' AND CAST(awp_postmeta.meta_value AS DATE) >= 'today' 

So somehow it seems that the strtotime() call isn’t happening, as ‘today’ is making it all the way to the SQL call. Hmmm. ??

@ajwe, Ah, I found the issue, thank you for your information. I will update the code in the next release. I should use 'DATE' === $refined_query['type'] instead of 'date' === $refined_query['type']. If you want to fix it right away, you could harded-code the fix at line 1993 in the custom-blocks.php file.

Phi.

OK, thanks, I see why that fixes it (strtoupper). I’ve made the change manually for now and that does resolve the SQL issue in the error log. I do have to make sure the DATE FORMAT field is explicitly set to Y-m-d; for some reason when I blank that field out (which should pick the default), it goes back to finding no results.

(I do still have one other strange issue now: If I set the date to “now” or “tomorrow” or “yesterday” or “2023-12-27” or even “today UTC” it works, but when I set the date to “today” it returns no results. Very confusing, even though when I use php interactively it seems fine. At least I can work around this for the moment by just using “now”.)

@ajwe Thank you so much for the detailed insight. I will fix the blank value for the date format.

Regarding the ‘today’ issue, I bet it is a time zone issue. You could use ‘today UTC’ or ‘tomorrow’ for that if that is the case.

Hi @ajwe,

I’ve just released a new version that fixes the query loop issue. Please update your site to get the fix. Thank you again for reporting it.

Best regards and Happy New Year.

Phi.