It’s important that you ensure that your dates are stored in a format that is compatible for sorting, either the MySQL default of YYYY-MM-DD or as a Unix timestamp. I prefer the MySQL format because it is both sortable and readable by a human. As long as the data is stored correctly, you can follow the example here:
https://code.google.com/p/wordpress-custom-content-type-manager/wiki/get_posts_examples#List_Posts_in_a_Date_Range
Thread Starter
nowton
(@nowton)
It was stored as the default, now I changed it to MySQL. However on building a query and adding a datefield to filter on, the result does not filter. Any insights? Mind you I’m using a Custom Datefield, not the regular post_date.
<?php
$today = date('Y-m-d'); /* yyyy-mm-dd */
//echo $today;
$Q = new GetPostsQuery();
$args = array();
$args['post_type'] = 'visiting-researcher';
$args['enddate']['<'] = $today;
$args['order'] = 'ASC';
$results = $Q->get_posts($args);
foreach ($results as $r):
?>
<article>
<h1><? print $r['post_title']; ?></h1>
<span class="function"><? print $r['bio']; ?></span>
<p class="date"><? print $r['startdate']; ?> – <? print $r['enddate']; ?></p>
<? boilerplate_posted_in(); ?>
<? the_content(); ?>
</article>
<? endforeach;
?>
You can use the debug method:
// ...
$results = $Q->get_posts($args);
print $Q->debug();
That will give you the raw queries and you can run them on your MySQL instance. That’s the best way to test this — it’s forces you to check the values in your fields too.
Thread Starter
nowton
(@nowton)
Slight adaptation of my code, by querying researchers that started this year $args['startdate']['starts_with'] = '2014-'; and debug enabled gives me this:
Summarize Posts
Errors
There were no errors.
Warnings
Search parameters ignored: search_term and search_columns must be set.
Notices
Filtering on direct column/value: startdate: starts_with 2014-
Execution Time
0.00 seconds
Arguments
post_type: Array (visiting-researcher)
startdate: 2013-
order: ASC
For more information on how to use this function, see the documentation for the GetPostsQuery::get_posts() function.
Raw Database Queries
SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID=wp_postmeta.post_id WHERE ( 1 AND (wp_postmeta.meta_key = 'startdate' AND wp_postmeta.meta_value LIKE '2014-%') AND wp_posts.post_type NOT IN ('revision','nav_menu_item','tribe_venue','tribe_organizer','sd2_custom_slide','slidedeck2') AND wp_posts.post_type IN ('visiting-researcher') AND wp_posts.post_status IN ('publish','inherit') ) GROUP BY wp_posts.ID ORDER BY wp_posts.ID ASC
Comparable Shortcode
[summarize-posts post_type="visiting-researcher" startdate="2013-" order="ASC"]
Results
Array
(
)
I think I have an issue with the way dates are stored. Please advise, thanks.
It’s tough if the dates aren’t stored in a sortable format. The only way to really fix that is to go through each field and convert the date. In lieu of having a script that will do that, you’d have to edit the field definition so it uses a valid format, and then re-edit and re-save each post using that field. Painful, I know.
Thread Starter
nowton
(@nowton)
So you share my assumption that the dates may not be stored in the proper format? Or is there a way to tell by looking at the database contents?
Yes. Look at the values in the wp_postmeta table. A sortable format would be something like a Unix timestamp (e.g. 12345678) or a MySQL datestamp (e.g. 2014-04-21)
Thread Starter
nowton
(@nowton)
Metavalues for that look like dd/mm/yyyy. So setting the format in CCTM _after creating the field does not alter the format in the database (!). Not sure what to think of that, although I am happy I found something. Can I just alter the format in the database, or are there other measures involved?
It can be disastrous for the plugin to attempt to alter formats after they have been entered because you run the risk of corrupting data, so that was very much a conscious decision to error on the side of safety. Values are changed only when creating new posts or editing existing ones and the date value is re-written to the database — that’s what I was referring to with my earlier comment about this situation being painful.
The messaging for date fields does recommend to use the MySQL date format because it’s sortable, but I still get posts like this fairly regularly so it would make a good feature request if you care to file it.
Thread Starter
nowton
(@nowton)
I’ve changed those (46) fields in the db manually and managed to get the views to work. I of course also changed the format for the field so new posts will have the correct format.
I’m not really sure what the feature request would entail: make it more obvious to the inconspicuous developer, or have the default set to mysql.
I added the feature request for you:
https://code.google.com/p/wordpress-custom-content-type-manager/issues/detail?id=554
MySQL should already be the default date format — if that’s not coming up, that’s a bug.
Thread Starter
nowton
(@nowton)
It wasn’t the default in my case. (dd/mm/yyyy was). And thanks for the request, I’ve starred it.
Hi,
are there any news about this issue.
I am facing the problem that I have to change the dates frequently and ordering is not working at all.
Thanks.
In the bug-tracker, you can choose to follow the particular request/issue: updates will be sent when I have time to work on the issue.
