Skip Navigation

[Resolved] Query expiring and expired posts

This thread is resolved. Here is a description of the problem and solution.

Problem:
the user is using a form with the expiration feature. He then queries posts on a view based on the expiration date. But he encounters some issues because the site is using a different timezone than UTC.

Solution:
Expiration dates are stored as timestamp, hence in the UTC timezone.

But the view's query using "NOW" and "SECONDS_FROM_NOW" are calculated in the site's timezone. We'll need to hook into the query and change these dates to UTC.

Check this sample code:

add_filter( 'wpv_filter_query', 'change_date_filters_for_expiring_jobs', 10, 3 );
  
function change_date_filters_for_expiring_jobs( $query_args, $view_settings, $view_id ) {
  // return $query_args;
  if ( $view_id == 10351) {
    $value = $query_args['meta_query'][0]['value'];
    $values = explode(",", $value);
    $value = ( ( (int) $values[0] ) - 7200 ) . "," . ( ((int) $values[1]) - 7200 );
    $query_args['meta_query'][0]['value'] = $value;
  }  
   
  return $query_args;
}

Note that this code assumes that there is only one meta_query on the view. If the view queries another custom field, this code should be modified and adapted

This support ticket is created 3 years, 7 months ago. There's a good chance that you are reading advice that it now obsolete.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

Sun Mon Tue Wed Thu Fri Sat
9:00 – 13:00 9:00 – 13:00 9:00 – 13:00 9:00 – 13:00 - - 9:00 – 13:00
14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 - - 14:00 – 18:00

Supporter timezone: Africa/Casablanca (GMT+00:00)

This topic contains 25 replies, has 3 voices.

Last updated by simonM-5 3 years, 6 months ago.

Assisted by: Jamal.

Author
Posts
#1770799

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

Hello Simon,

Your website is still down, so I'll put this ticket as waiting for your feedback until you replied that the server is available.

Looking forward to your reply.

#1771845

HI Jamal

We're back online.. 😅Our server was hit by a virus. The virus injected code into some of the files in our wp-content folder, including Toolset Blocks. For that reason I've tried to only re-install what I consider to be essential plugins. That means that File Manager is no longer installed. If you need FTP access, please let me know in a private reply and I can create an FTP account for you.

Thanks and best regards
Simon

#1772295

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

Hello Simon,

From what I gathered so far, it seems that the expiration date is always saved in UTC timezone. I changed the Timezone for the site to UTC and the view has worked.
But I am still not sure if I get to the bottom of this issue.

I will need FTP access in order to:
- Put a phpinfo script to get information about the server timezone(vs WordPress timezone).
- Put the adminer script to be able to view the database.

I might also need to take a copy of your website for my local debugging and eventually if I needed to escalate this issue to our 2nd Tier.

Your next reply will be private to let you share FTP credentials safely.

#1773503

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

I want to let you know that I am still working on this. I did not resolve it yet, but I'll get back to you as soon as I have something to share.

#1778531
Screenshot 2020-09-16 at 12.53.54.png

HI Jamal

Unfortunately our site is under attack from backdoor viruses again, and has become inaccessible. I saw this warning in WordFence today, related to adminer script. It seems there may be a vulnerability there too. Unfortunately I can't get into the site right now to fix it.

I'll let you know again once this is resolved. If you have finished with adminer, can you please remove if from our server

Kind regards
Simon

#1780035

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

Thank you Simon for your feedback.

I worked for so long last Friday and Saturday to find the cause of this issue or to find a workaround to no avail. I wanted to try this locally, but I am not sure if I would be able to build a server on your timezone. That's why I preferred to use your server and to use Adminer to access the database.
I'll use the latest release of Adminer from now on, and I'll delete, every time I complete my debugging.

For now, let's set this ticket as waiting for your feedback until you confirm that I can work on your server.

Thank you!

#1784887

Hi Jamal

Our site is up again with a few issues still to resolve since it was attacked by the virus. So you have a green light again!

Kind regards
Simon

#1787559

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

Hello Simon,

The expiration date is saved as a timestamp, so by design, it is on the UTC timezone. But the view's query filter functions such as "NOW" and "SECONDS_FROM_NOW" are calculated based on the website timezone.
We'll need to change those dates (min and max)on the query to a UTC time, I was able to do it for the expiring jobs view with the following custom code, check the "change-date-filter-for-expiring-jobs" snippet:

add_filter( 'wpv_filter_query', 'change_date_filters_for_expiring_jobs', 10, 3 );
 
function change_date_filters_for_expiring_jobs( $query_args, $view_settings, $view_id ) {
  // return $query_args;
  if ( $view_id == 10351) {
    $value = $query_args['meta_query'][0]['value'];
    $values = explode(",", $value);
    $value = ( ( (int) $values[0] ) - 7200 ) . "," . ( ((int) $values[1]) - 7200 );
    $query_args['meta_query'][0]['value'] = $value;
  }  
  
  return $query_args;
}

Please note, that this code is not optimized and will work if there is no other filter on any other custom field. Otherwise, we need to modify the code, so, it will update the array of the expiration time custom field.

I'll let you test this from your side and I'll wait for your feedback on this solution. Let me know if it suits you or if you need further assistance with it.

#1788631

Hi Jamal

Thanks for that, however before testing I already see a potential flaw in this code. The 7200 is 2*3600 (ie number of seconds in 2 hours). However, we cannot hardcode 7200 seconds as this will not always be correct. In particular in the EU, Daylight Saving thankfully looks like it will be abolished, so that 2021 will be the last year that the clocks change seasonally. (see hidden link)

When daylight saving is lost again in Germany on 25 October this year, the Central European Time (CET) offset will only be 3600 seconds, ie we will only be 1 hour ahead of GMT.

Is there a way to query the offset and make that a variable, so the formula would be something like UTC time - Offset seconds?

I'm not sure I 100% understood what you mean here by "not optimised":
"Please note, that this code is not optimised and will work if there is no other filter on any other custom field. Otherwise, we need to modify the code, so, it will update the array of the expiration time custom field."

Thanks and regards
Simon

#1789439

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+00:00)

You are totally right Simon, hardcoding the value 7200 is not correct. You can change by:

wp_timezone()->getOffset(new DateTime())

Code would be:

add_filter( 'wpv_filter_query', 'change_date_filters_for_expiring_jobs', 10, 3 );
  
function change_date_filters_for_expiring_jobs( $query_args, $view_settings, $view_id ) {
  // return $query_args;
  if ( $view_id == 10351) {
    $value = $query_args['meta_query'][0]['value'];
    $values = explode(",", $value);
    $offset =  wp_timezone()->getOffset(new DateTime());
    $value = ( ( (int) $values[0] ) - $offset ) . "," . ( ((int) $values[1]) - $offset );
    $query_args['meta_query'][0]['value'] = $value;
  }  
   
  return $query_args;
}

I'm not sure I 100% understood what you mean here by "not optimised"
My code, at line 6, assumes that the condition on the time interval is the only condition on meta keys(custom fields). You can see how it uses the index 0:

$value = $query_args['meta_query'][0]['value'];

For this code to be "correct" and works with multiple condition on custom fields, it should search inside the array $query_args['meta_query'] for the array that have a key equals to "_cred_post_expiration_time", then change the value with the calculated numbers(timestamp +/- offset).

I hope this helps. Let me know what you will get.

#1793351

Hi Jamal

Great! That appears to be working with the offset, so I think I can manage the rest from there. I will duplicate this code for Nanny Ads, if I come up with any problems I will open another ticket.

Kind regards
Simon

This ticket is now closed. If you're a WPML client and need related help, please open a new support ticket.