[Resolved] _cred_post_expiration_time is not respecting timezone or problem with view query

[Author]

Posts

[simonM-5]

Hi Support

Either the _cred_post_expiration_time is not respecting the WordPress timezone settings or there is a problem with view query.

Reproducible example:

1) I set our Post Type Job Ads to expire within an hour of creation via Toolset Forms.

2) The View "Edit My Job Ads View" is set to show all active Job Ads created, filtered using

_cred_post_expiration_time is a number greater than NOW().

3) The View "My Expired Job Ads View" is set to show all expired Job Ads created filtered using

_cred_post_expiration_time is a number less than NOW().

4) I created a Job Ad at 12:08, which it shows correctly as expiring one hour later at 13:08 (see screenshot), however the Ad is appearing in My Expired Job Ads at the time 12:17. It should not "move" from Edit My Job Ads View to My Expired Job Ads View until 13:08.

Kind regards
Simon

[Christian Cox]

Hi, I think there is a problem in a View query filter set to test "NOW". On your site, and in my local tests, the value of "NOW" is inaccurate in the resulting MySQL query. When I test the View with Views debugging turned on, I can see that the expiration date filter is querying for posts with expiration date lower than the Unix timestamp 1612804935, which is exactly 1 hour from the time I loaded the page. So it seems that the query is calculating "NOW" incorrectly. I had similar results in my local tests, where the NOW value seems to be inaccurate. The offset is different, but I think that is because our sites are in different timezones.

Let me escalate this to my 2nd tier support team for additional investigation, and I'll give you an update as soon as I can.

If you need an immediate solution, it might be best to delete the Query Filter based on the expiration time field, and add a filter manually using the wpv_filter_query API, something like this:

add_filter( 'wpv_filter_query', 'tssupp_expiry_lower_than_now', 99, 3 );
function tssupp_expiry_lower_than_now( $query_args, $view_settings, $view_id ) {
  $views = array( 10430 );
  if ( in_array( $view_id, $views ) ) {
     $query_args['meta_key'] = '_cred_post_expiration_time';
     $query_args['meta_value'] = time();
     $query_args['meta_compare'] = '<';
     $query_args['meta_type'] = 'numeric';
   }
   return $query_args;
}

[Christian Cox]

I've spoken with my 2nd tier team about this, and they confirm that a wpv_filter_query workaround is required to filter by _cred_post_expiration_time. The problem lies in how our custom fields store dates in the database. The Unix timestamps are not stored as UTC timestamps, but with a timezone offset applied. So to compensate for that, the NOW feature does not return a UTC timestamp either, but a timestamp with a timezone offset applied. This helps facilitate custom field date query filters. However, post expiration timestamps ARE stored in UTC, which leads to inconsistent results when filtering by date using that post expiration custom field. Until the date field storage issue is resolved, the custom code I mentioned before is required to accurately filter by post expiration date/time. Our developers have acknowledged the inconsistency and are working to improve date filters and how date custom field values are stored in the database, and hope to have some updates in a future version of the software.

[simonM-5]

Hi Christian

Thanks for all the info and the code and hopefully this will be addressed soon in a future release as it's a super useful feature!

I created two new code snippets:
- post-expiry-lt-now-view-filter
- post-expiry-gt-now-view-filter

However they are showing Error when I save them. As far as I can see, I just changed the names of the functions and filters and added in commented lines of which views should be affected. Otherwise it should work.

I thought it might be conflicting with my previous View filters created via the GUI, which I removed. Then I re-saved both custom snippets, but apparently the code still has a problem.

Could you please have a quick peek at the Custom Code snippets to understand why they are producing errors?

Thanks and best regards
Simon

[Christian Cox]

The error messages shown are:

[2021-02-10 14:40:43, ajax] syntax error, unexpected ';' in /var/www/web24632223/html/dev/wp-content/toolset-customizations/post-expiry-gt-now-view-filter.php on line 20
[2021-02-10 21:04:42, admin] syntax error, unexpected ';' in /var/www/web24632223/html/dev/wp-content/toolset-customizations/post-expiry-lt-now-view-filter.php on line 16

The errors point to lines 20 and 16, but the problem is missing code on lines 19 (gt) and 15 (lt). Looks like the "if" was omitted in these snippets.

[simonM-5]

HI Christian

Thanks, that was it!

What is the expected behaviour of the plugin once the Ad has expired? It would appear to me that the expiration date is getting NULLed or sent back to Unix starting time of 1/1/1970. I am using the same code to reference the expiry timestamp in the "active" ads and expired Ads. Can you confirm if the expiration timestamp is being deleted on expiration? If it still possible, we would prefer to be able to display the ACTUAL expiry timestamp under Expired Ads also.

My Expired Job Ads View
[cred-post-expiration id="[wpv-post-id]" format="D d/m/Y H:i"]

Edit My Job Ads View
[cred-post-expiration id="[wpv-post-id]" format="D d/m/Y H:i"]

Thanks and regards
Simon

[Christian Cox]

Can you confirm if the expiration timestamp is being deleted on expiration?
Yes, the system is designed to set the expiration timestamp to 0 after the post has expired.

If it still possible, we would prefer to be able to display the ACTUAL expiry timestamp under Expired Ads also.
Unfortunately it is not possible with the cred-post-expiration shortcode, since this shortcode is designed to display a date based on the _cred_post_expiration_time postmeta value, and as you've seen that value is reset after expiration. If you want to keep a record of the post's original expiration timestamp, I think you would need to store that value as a timestamp in a separate custom field when the post is created or edited with Forms. Then use a custom shortcode to display that value as a formatted date. Let's say you want to use a hidden custom field with the slug "_tssupp_copy_cred_expiry". You can use the cred_submit_complete hook to set that custom field value automatically:

add_action('cred_submit_complete', 'tssupp_copy_cred_expiry',10,2);
function tssupp_copy_cred_expiry($post_id, $form_data)
{
  $slug = '_tssupp_copy_cred_expiry';
  $forms = array( 123, 456 );

  if ( in_array( $form_data['id'], $forms ) )
  {
    // copy the original expiration timestamp into a hidden custom field
    // for future reference, since original timestamp is reset upon expiry
    $expiry = get_post_meta( $post_id, '_cred_post_expiration_time', true );
    if( $expiry && $expiry !='0' ) {
      update_post_meta( $post_id, $slug, $expiry );
    }
  }
}

You would replace 123, 456 with a list of Form IDs. Any Form IDs you include here will trigger this code to copy the post's expiration timestamp into the custom hidden field.

Then to display the custom hidden field expiration date, you could use this generic timestamp field formatting shortcode:

function format_timestamp_field_func($atts) {
  $a = shortcode_atts( array(
    'format'=> get_option('date_format'),
    'slug'  => '',
    'postid'=> 0
  ), $atts );

  $fieldDate = get_post_meta( $a['postid'], $a['slug'], true );
  if ( !$fieldDate )
    return;
  $date = date($a['format'], $fieldDate);
  return $date;
}
add_shortcode('format-timestamp-field', 'format_timestamp_field_func');

You would use the shortcode like this to display the formatted expiration date:

[format-timestamp-field format="D d/m/Y H:i" slug="_tssupp_copy_cred_expiry" postid="[wpv-post-id]"][/format-timestamp-field]

[simonM-5]

HI Christian

Thanks for response. Seems like a viable solution. While I'm trying to get that working, I wanted more clarification on one statement you made:
"you would need to store that value as a timestamp in a separate custom field when the post is created or edited with Forms"..

What is the expecting behaviour of the plugin when a form is edited? Does it change the original expiry date of the form?

Example: Forms set to expire after 10 days. I create a form on the 1st of January, it gets an expiry date of the 10th of January. On the 5th of January I edit the form. How does the plugin handle the expiry date - does it keep it at 10 Jan or does it make it 15 Jan?

Kind regards
Simon

[Christian Cox]

What is the expecting behaviour of the plugin when a form is edited? Does it change the original expiry date of the form?
I think you are asking what happens to the original expiration setting when a post is edited with an Edit Post Form. It depends on the expiration settings in the Edit Post Form. You can modify the expiration date using the expiration settings in the Edit Post Form, or if you turn off expiration in the Edit Post Form the original expiration date will be unchanged.

Forms set to expire after 10 days. I create a form on the 1st of January, it gets an expiry date of the 10th of January. On the 5th of January I edit the form. How does the plugin handle the expiry date - does it keep it at 10 Jan or does it make it 15 Jan?
If the Edit Form is set to "post will expire in 10 days after the form is submitted", the expiration will be updated to the new expiration date. If "Set expiration date for post created or edited by this form" is unchecked in the Edit Form, the original expiration date will be unchanged.

[simonM-5]

Hi Christian

Thanks for the updates and the very useful information.

I created the custom code and it works well - in part, at least. It only stores the expiry timestamp for the post which was created. However, WPML has to duplicate the post so that we can have the post in German as well, and this is not getting replicated to its language duplicate.

So for example, if we create a post in English (our primary language), the expiry date is shown correctly on the English post. However switching to German and viewing the WPML language duplicate, it shows null for the same field. Vice versa starting in German.

Would it be possible to approach the solution in a similar fashion from a slightly different angle:
1) Create for example two new custom fields "Job Ad Expiry" and "Nanny Ad Expiry" via the Toolset GUI on the Post Types Job Ad and Nanny Ad respectively
2) Use some kind of custom code (or can Toolset do hidden fields on the form?) to copy _cred_post_expiration_time to the new custom field?

Then WPML can do its job automatically and duplicate the post, including the new custom field "Job Ad Expiry"/"Nanny Ad Expiry" to its language duplicate...

Thanks and regards
Simon

[Christian Cox]

You can copy the original expiration time into custom fields on each post type. That should be possible by changing the code I provided earlier just a bit. Here's a sample of two code snippets, one for job ads and another for nanny ads.

add_action('cred_submit_complete', 'tssupp_copy_job_cred_expiry',10,2);
function tssupp_copy_job_cred_expiry($post_id, $form_data)
{
  $slug = 'wpcf-jobexpiryfieldslug';
  $forms = array( 123, 456 ); // job ad forms
 
  if ( in_array( $form_data['id'], $forms ) )
  {
    // copy the original expiration timestamp into a custom field on job post
    // for future reference, since original timestamp is reset upon expiry
    $expiry = get_post_meta( $post_id, '_cred_post_expiration_time', true );
    if( $expiry && $expiry !='0' ) {
      update_post_meta( $post_id, $slug, $expiry );
    }
  }
}

add_action('cred_submit_complete', 'tssupp_copy_nanny_cred_expiry',10,2);
function tssupp_copy_nanny_cred_expiry($post_id, $form_data)
{
  $slug = 'wpcf-nannyexpiryfieldslug';
  $forms = array( 234, 567 ); // nanny ad forms
 
  if ( in_array( $form_data['id'], $forms ) )
  {
    // copy the original expiration timestamp into a custom field on nanny post
    // for future reference, since original timestamp is reset upon expiry
    $expiry = get_post_meta( $post_id, '_cred_post_expiration_time', true );
    if( $expiry && $expiry !='0' ) {
      update_post_meta( $post_id, $slug, $expiry );
    }
  }
}

You would replace jobexpiryfieldslug with the slug of the custom field on the Job post, and replace 123, 456 with a comma-separated list of New Job Form IDs. You would replace nannyexpiryfieldslug with the slug of the custom field on the Nanny post, and rpelace 234, 567 with a comma-separated list of New Nanny Form IDs.

[simonM-5]

Hi Christian

Thanks for the update.

I created two new custom fields:
wpcf-family-ad-expiry and wpcf-nanny-ad-expiry

and am able to populate them both in the database with the correct timestamp. However, it is still not appearing on WPML language duplicates.

When I query the database directly (SELECT * FROM `wpdev_postmeta` where post_id = 14076) for a language original I see the field wpcf-family-ad-expiry and that it is correctly populated, but on the language duplicate (SELECT * FROM `wpdev_postmeta` where post_id = 14077), I don't even see the new column, despite it being set to "Copy from original to translation", which I cannot explain logically.

Kind regards
Simon

[simonM-5]

Sorry forgot the other screenshots...

[Christian Cox]

Are you saying the posts are duplicated into other languages programmatically when the Form is submitted? If so, the problem is most likely a timing issue...the duplication process is run before the process that copies the expiration timestamp value. The custom code we have implemented in the current ticket uses the cred_submit_complete hook to trigger the timestamp copy process. If the cred_save_data hook is used to trigger the automated language duplication process, the custom field value has not yet been set when the duplicate is created because cred_submit_complete always runs after cred_save_data. Neither hook is wrong per se, it's just a question of timing and when you want to run these processes.

So depending on the other custom code you have in place on the site, the solution could be:
- Update the expiration date copy process to use the cred_save_data hook instead of the cred_submit_complete hook. You must use a priority of at least 11 to access the _cred_post_expiration_time value. You should check the priority of the language duplication process as well to ensure the language duplication process runs after the custom field copy process.

- or -

- Instead of changing the cred_submit_complete callback to cred_save_data, you could add code in the cred_submit_complete callbacks that uses the WPML API to find the translation duplicate post IDs and use update_post_meta to manually set the custom field value in all the duplicate language posts. WPML's APIs for getting translation group IDs and element translations are documented here:
https://wpml.org/documentation/support/wpml-coding-api/wpml-hooks-reference/#hook-1215366
https://wpml.org/documentation/support/wpml-coding-api/wpml-hooks-reference/#hook-1215380

[simonM-5]

Hi Christian

Hm, that seems logical. It is more likely a timing issue as to which happens first.

We have other custom code from Jamal in place which duplicates all posts to the other languages in the snippet "func-create-ad-language-duplicates-on-submission". There it would appear that the WPML API is being used, if I'm not mistaken.

To try the first method first, does that mean I just have to replace the text "cred_submit_complete" to "cred_save_data" in the custom code for the current ticket (stored in code snippet "preserve-ad-expiry-date")?

I'm not sure what you are talking about when you talk about "priority" in the sentences?
"You must use a priority of at least 11 to access the _cred_post_expiration_time value. You should check the priority of the language duplication process as well to ensure the language duplication process runs after the custom field copy process."

Thanks and regards
Simon