I have a Metabox CPT which has a datepicker field (sermon_date) in it. I haven’t changed the format of how the date is stored. I have a query loop on the CPT and am filtering the results using the following meta query:
meta key: sermon_date
meta value: {current_date:Ymd}
compare: equal
type: char
The problem is that I have a CPT post with today’s date in sermon_date but it is not being returned when compare is equal. It is being returned if I use lesser or equal. If I use greater than or equal it is not returned but two posts with dates later than today are.
Is it because time is being used in the query somehow (even though I specify Ymd) so even though the date is the same, the time is not?
Also, why do I have to pick char to get this to work? Using date and datetime returns nothing.
This must be due to the current_date function and compare of char. I wrote my own php function to get the date and when I echo’d this in the meta value field and set compare to date everything worked.
Are you sure that the default Metabox Date format is Ymd?
When I create a new Metabox Date field, the default format is Y-m-d. Accordingly, I am not surprised that it cannot be compared with Ymd because the formats are not identical
Ooh, this is strange. I had set in Metabox the dateformat of the datepicker to be dd-mm-yy but didn’t change the save format. I checked in the database postmeta table and the meta-value is being displayed in the datepicker format (so 21-04-2023). I removed the format and then created a new post and the value is stored as 2023-04-21. According to the metabox help, the datepicker format only affects how the date is displayed not stored but it seems to be affecting Bricks.
So as I test I removed the formatting of the datepicker and deleted all the posts. Then created some new posts before, after and on today’s date and set the meta query value to {current_date:Y-m-d}, the compare to equal and the type to date. In the canvas the correct posts are returned. Changing the compare to greater than or equal yields the correct results.
I then set the datepicker format to dd-mm-yy and created a new post. I left the meta query parameters the same and the new post did not show (as expected) but the correct previous ones were still. I then changed the meta query value to d-m-Y and all my previous posts were returned which is not correct and the new post was not returned. I guess formatting issues are causing all the previous posts to be returned but it doesn’t explain why the new one isn’t. I tried dd-mm-yy and d-m-Y but this didn’t work.
It seems from this that in Bricks the year always has to come first in the database and in the value query.
the only thing that matters is that the format the dates are stored in the db (which is your responsibility using the custom field settings as described in the meta box documentation) match your comparison value {current_date:DB_DATE_FORMAT}. The created query does not care about any specified formats. It fetches the date field’s value 1:1 as stored in the database.
I’d definitely recommend a format like Ymd or Y-m-d though since you’ll have a hard time correctly sorting the dates in other cases.
Then tell me the format I should be using for {current_date} when dates in the database are in dd-mm-yyyy (21-04-2023) format. Did you actually try my tests too?
Y-m-d certainly works but I can’t get anything else to work.
OK, I set the save format to Y-m-d and it works. Still not sure why I have to set the save format as well, I assumed it would be the standard Y-m-d despite what was set for the date picker.
Thanks for the help.
glad you made it work. Did you have a look at the documentation link I sent in a previous comment? There you can see that you need to specify it.
However, this is the format that users see in the date picker. You might want to save the value in another format, like 2022-10-20 , which allows you to sort or query posts by date. To do that, set the value of “Save format” to Y-m-d . Unlike the above, the saved format is the PHP date format , which is similar to PHP’s date() function.
Yeh that’s what put me onto it. Clearly the datepicker format and save format are the same unless you explicitly set the save format. I didn’t think/realise that changing the datepicker format also affected the save format and that I would have to set the save format. I assumed it was always Y-m-d by default unless changed.
