[Resolved] Sorting a View by a custom date field is not in correct order

[Author]

Posts

[Gavin]

Hello

I have a CPT called Chat in a relationship with a CPT called Reply.

When a Reply is posted I use PHP to update a Custom Date Field called 'Latest Reply' in the Chat post with the current date and time.

I also populate this field when a new Chat is posted.

Everything in the back end appears to work and look fine. If I edit a Chat post I can see the 'Latest Reply' field has been populated with the correct date and time.

Yet, when I use a view and sort by the Custom Date Field (Latest Reply) the front-end displayed order is not correct.

I am not using any third-party sorting plugins (in fact I am only using Toolset) and I have checked that it is not my theme causing the issue.

I am guessing it might be something to do with how the date and time is stored in the custom date field? I will attach a screenshot of my PHP to show how I am storing the date in the custom date field.

Thanks

Gavin

[Gavin]

I should point out that in the admin panel, if I sort by the 'Latest Reply' field then the order is correct. I did see a support ticket where Beda explained that the WordPress admin sorts fields by a string and that Views uses the numerical value.

[Gavin]

Here are the first three posts with the RAW date/time stamps for the custom 'Latest Reply' field above each Chat post.

As you can see, if being displayed in descending order, as per my View, then the Chat post currently displayed second should be displayed first.

[Gavin]

It should be noted that some Chat posts have an empty custom field for 'Latest Reply'. I did wonder if this is causing the sorting to become corrupted in some way so moved posts with the empty Latest Reply fields but the sorting was still incorrect.

[Gavin]

Also... FYI... if I set the View to display by post date, all the posts appear, yet if I set the view to display by the 'Latest Reply' custom field, the pagination does not work, which might point to there being a bug.

[Minesh]

Hello. Thank you for contacting the Toolset support.

When you set your view to order by custom field, it will only count the entries where the custom field value is exists.

I'm not sure now where you are with the issue and where exactly you are at this time.

Can you please share problem URL where I can see the issue as well as admin access details and share what is your expected result.

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I have set the next reply to private which means only you and I have access to it.

[Gavin]

The View you need to look at is called 'List all FabChats' - view_id=11156

[Gavin]

Hi Minesh. I am continuing to explore what is going on...

1) The 'Latest Reply' date custom field was showing up when I edited a Chat CPT but NOT in the admin panel. If I updated a Chat then the 'Latest Reply' date appeared in the admin panel.

This would point to it being a problem with my PHP updating the field incorrectly? Please can someone check my PHP (as per the first post in this thread?) I currently do not understand how the date field can display correctly in the Chat CPT and display on the front end correctly, BUT not in the admin panel unless I 'update' the post.

So...

2) I manually updated every Post and ensured the 'Latest Reply' field was populated with a date.

The Sorting by 'Reply Date' now works.

I am now going to test what happens if I 'reply' or post a new chat and test if the PHP is causing the issue.

[Gavin]

BTW: The pagination issue was my mistake. Please ignore that issue.

[Gavin]

Okay. I am narrowing this down.

If I create a NEW Chat post with the title 'Test 1' my PHP is writes todays date and time into the 'Reply Date' field as I can see it when I edit the Chat - see attached image 'CPT'.

BUT... the date is not appearing in the admin panel - see attached image 'Admin Panel' and in the front end sorting by Reply Date is not including my 'Test 1'.

HOWEVER... if I UPDATE the Chat post 'Test 1' manually then that fixes the issue - see attached image 'Updated Post'.

[Gavin]

Note:

I have tried both cred_save_data and cred_submit_complete

and tried writing with

$todaysDateTime = current_time('mysql');

and

$todaysDateTime = wp_date("M j, Y, g:i:s A");

[Minesh]

I see you used: current_time
- https://developer.wordpress.org/reference/functions/current_time/

As per the function "current_time" it will return the date string for 'mysql' argument but Types custom date field requires timestamp and as per the WordPress 5.3 its not recommended to use current_time('timestamp');
=> https://make.wordpress.org/core/2019/09/23/date-time-improvements-wp-5-3/

Types date field stores Unix Timestamp as the date value to the database. So you will have to store the Unix Time Stamp with your custom date field: wpcf-latest-reply

What if you try to save it like:

$todaysDateTime = time();
update_post_meta( $post_id, 'wpcf-latest-reply', $todaysDateTime );

Can you please update your code where required to update the timestamp for the custom field "latest-reply" and then try to create a new entry and check. For existing entries you will have to update the post from backend as those posts (reply) as incorrect date value and Types custom date field requires timestamp.

[Gavin]

Thanks Minesh!