[Resolved] Query expiring and expired posts

[Author]

Posts

[simonM-5]

Hi Support

I am struggling to get posts displayed in a view which are about to expire.

I've already set up automatic expiration of posts of type Job Ads as described in https://toolset.com/course-lesson/setting-up-automatic-post-expiration, which is a nice, useful feature but lacks some fundamental functionality in my opinion, resulting in it being difficult to work with the Expiry Date in queries later.

The value stored in wp_postmeta as a UNIX number is not easy or intuitive to use as query filter condition without using Custom Code again.

Our requirements are:
(1) display the Job Ad post expiry date on a list of posts. I found how to do this already, using the shortcode [cred-post-expiration id="[wpv-post-id]" format="m/d/Y"]. It would also make sense to add this to your documentation somewhere in https://toolset.com/course-lesson/setting-up-automatic-post-expiration.
(2) display a view filtering Job Ad posts expiring in the coming 30 days
(3) display a view filtering Job Ad posts which have expired

Ideally, this should be an easy, intutive task, eg by choosing expiry date in the query filter between TODAY and TODAY+30 for (2) and > TODAY for (3), however the expiry date is not available in the query filter section picklist.

I thought of several approaches to how this could be solved:

1) Toolset Code Change
- Toolset makes this date available somehow as an option in the query section, eg as "Custom field -cred_post_expiration_date"

2) On saving the form, use a hidden field to generate a "TODAY + x DAYS" and store it as eg POST EXPIRY DATE on the Job Ad post type.

3) I then thought of approching it by querying using the Post Date, since it is the reference point for the expiry date anyway, however that failed, due to how the Post Date query works.
3a) In our dev environment all of our test Job Ads are created in this month, August 2020. So, I tried, for example, Post Date with Day between PAST_ONE 30 and CURRENT_ONE, (thinking this was the same as between TODAY-30 and TODAY) but this didn't work with any value above the current day. I suspect this may be because in the background it is doing 23 - 30 = -7 and not being able to interpret -7 as a real day of month.

3b) I can't use BETWEEN (Day=CURRENT_ONE and Month=CURRENT_ONE) and (Day=CURRENT_ONE and Month=PAST_ONE(1)), since this would not work for all days in the calendar, especially February. Also not clear how it would handle posts in January, giving perhaps 1 - 1 = 0 for the month.

3c) Similary post date older than between 155 and 185 days wouldn't work for the same reasons.

Am I overthinking this or have I missed something basic?! Or is there any easy way to achieve our simple requirements using existing query functions without custom code again? I imagine the first thing all developers would try to do using the Expiry Date is to query a list of expiring and expired posts.

If I am right about how the Post Date filtering is working currently, then please rename the following elements in the user interface to reflect what we are actually specifying for the query, valid values in brackets would help greatly here. It was already partly done for "Day of the year" and "Day of the week" but omitted for all other elements. This should reflect what is described in https://toolset.com/documentation/user-guides/views/filtering-views-query-by-date/.

a) Year >> OK
b) Month >> Month of year (or specify 1 - 12 in brackets)
c) Week >> Week of year (or specify 1 - 53 in brackets), also please specify what calendar standard is being used in the documentation ... ISO?
d) Day >> Day of month (or specify 1 - 31 in brackets)
e) Day of the year >> (OK, but specify 1 - 366 in brackets)
f) Day of the week, this was clarified in documentation already ie 1 - 7, also depending on whether you think Sunday is start of week (ie a 1) or a weekend day (ie a 7)
g) Hour >> Hour of day (or specify 0 - 23 in brackets)
h) Minute >> Minute of hour (or specify 0 - 59 in brackets)
i) Second >> Second of minute (or specify 0 - 59 in brackets)

Thanks and regards
Simon

[Minesh]

Hello. Thank you for contacting the Toolset support.

(1) display the Job Ad post expiry date on a list of posts. I found how to do this already, using the shortcode [cred-post-expiration id="[wpv-post-id]" format="m/d/Y"]. It would also make sense to add this to your documentation somewhere in https://toolset.com/course-lesson/setting-up-automatic-post-expiration.
==>
Ok, that's for your suggestion. I will pass to our Doc team.

(2) display a view filtering Job Ad posts expiring in the coming 30 days
==> Handling this issue here.

(3) display a view filtering Job Ad posts which have expired
==>
Will split this question with new ticket once #2 is addressed.

Toolset forms store the post expiration value with the hidden custom field namely: _cred_post_expiration_time

Toolset offers the section from where you can activate the hidden custom field. You can follow the following doc and find the field "_cred_post_expiration_time" and activate that field.
=> https://toolset.com/course-lesson/using-third-party-hidden-fields/

Once you activate the hidden custom field "_cred_post_expiration_time" from above section, you will able to see this field "_cred_post_expiration_time" with your view's "Query Filter" section.

Now, as you know this field will have value of Unix timestamp. So you can add query filter to your view as per the following screenshot:
=> hidden link

Can you please configure and check that you managed to resolve the issue for your question #2.

[simonM-5]

Hi Minesh

Thanks for the handy tip, I didn't know that was possible in Toolset.

I have created two simple views to test:

my-expiring-job-ads-view
my-expired-job-ads-view

These can be accessed on the front end if you switch to user nativefamily6 and hover on the Ads button (megaphone icon) in the menu > Expired Job Ads or Expiring Job Ads.

I am displaying the UNIX number and the expiration date for testing purposes so I can check the results already.

1) I was using the hidden link page to convert UNIX timestamps to something human readable, and NOW appears to be 2h behind my actual time. From where is Toolset sourcing its timezone for calculations using NOW(), TODAY and so on?

2) Is it possible to display the current time to the user? I couldn't find an easy way to do this.

3) Finally, can Toolset do simple arithmetic calculations to display the number of days before expiry, so we can have the post display something like "will expire in x days", using the calculation
"_cred_post_expiration_time" - NOW()

Thanks and best regards
Simon

[Minesh]

1) I was using the hidden link page to convert UNIX timestamps to something human readable, and NOW appears to be 2h behind my actual time. From where is Toolset sourcing its timezone for calculations using NOW(), TODAY and so on?
==>
i'm not sure why you are seeing two hour time difference as - the time should be calculated from the timezone you set from your site's timezone you set from your WP Settings -> General page.

2) Is it possible to display the current time to the user? I couldn't find an easy way to do this.
==>
To display the current time by adding the custom shortcode:

function func_display_current_time(){
     return date('g i: A');
}
add_shortcode( 'display_current_time', 'func_display_current_time' );

And call the shortcode as:

[display_current_time]

3) Finally, can Toolset do simple arithmetic calculations to display the number of days before expiry, so we can have the post display something like "will expire in x days", using the calculation
"_cred_post_expiration_time" - NOW()
==>
Again, this will require custom shortcode to make that arithmetic calculation and display the date accordingly.

For example:

add_shortcode( 'expiry_days_remaining', 'func_expiry_days_remaining' );
function func_expiry_days_remaining ( $atts ) {
     
   $expiry_time = get_post_meta( get_the_ID(), '_cred_post_expiration_time', true );
   $timeleft =   time() - $expiry_time;
   return round ($timeleft/(60*60*24));
}

You can use the shortcode as:

will expire in [expiry_days_remaining] days

[simonM-5]

HI Minesh

Great, thanks for the code snippets, they will prove very useful. I think I can play with them on my own to get things just right, formats etc. Originally your code was showing negative numbers, but I can play with the formula and rounding and absolute numbers etc.

In the screenshot you can see the issue with the times being 2 hours out. In our WordPress settings, both are being shown. 😉 I just need to know which one Toolset is actually using (UTC or local) in the calculations to be sure I'm always using the correct formulae.

Thanks and regards
Simon

[Minesh]

Can you please try to set the timezone to UTC+0 and check if that helps - Please check the following screenshot.
=> hidden link

[simonM-5]

Hi Minesh

I tried setting to UTC+0 and also UTC+2 but still the time being output by the [current_time] shortcode is always 2 hours behind our actual current time, regardless of what setting I choose there (UTC+0, UTC+2 or Berlin)

Kind regards
Simon

[Jamal]

Hello Simon, as Minesh is on vacation, I'll continue with you on this ticket. I hope that's fine.

Can you update your custom code to use TimeZones, check these articles, they may help:
- hidden link
- https://stackoverflow.com/a/20289096
Check this example:

$tz = 'Europe/London';
$timestamp = time();
$dt = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
$dt->setTimestamp($timestamp); //adjust the object to correct timestamp
echo $dt->format('d.m.Y, H:i:s');

You can get the current WordPress timezone from the option "gmt_offset", but that will return a number.

<?php echo get_option('gmt_offset'); ?>

Let me know if this helps.

[simonM-5]

Hi Jamal

Also here, I will try out your suggestion late tonight or tomorrow.

Kind regards
Simon

[Jamal]

Thank you Simon!

I'll set this ticket as waiting for your reply, it should be kept open for 3 weeks.

[simonM-5]

Hi Jamal

I'm not a PHP programmer, but I tried a couple of methods in the code including a couple more which I got off the web. None was successful unfortunately.

I tried the function described in
hidden link
and
https://developer.wordpress.org/reference/functions/current_time/

but can't get either to work. Also tried modifying Minesh's code with the example you gave.

I think PHP is having conversion problems with the various variables, eg when it's expecting a number, it's getting a timestamp, or when expecting a string, it's getting an integer etc.

All attempts are commented out now, except for Minesh's original one, which is giving back the time -2 hours. The code snipped it called func-current-time.

Kind regards
Simon

[Jamal]

Maybe the time that you are trying to display has already been saved as 2 hours late. Or maybe the code is not correct.

Can you tell me where do you try to display this time? And what time, so I can look at the database level?

[simonM-5]

Hi Jamal

Sure, you can see I have added the shortcode [current_time] to hidden link, so if you log view the site as nativefamily6 and click the Ads icon in the menu > Expired Job Ads.

So for example, right now it is 17:00 in Germany, but [current_time] will always show me 15:00.

Kind regards
Simon

[Jamal]

Hi Simon,

It turned out that your website settings are on the UTC timezone, Once we change it to the Berlin timezone it gives correct results. And I have also updated the snippet to use wp_date, which is aware of the site timezone.
Please check if that's what you are looking for or not. Note that I have changed the site timezone, let me know if that's what you want.

Best regards,
Jamal

[simonM-5]

Hello Jamal

Our site was originally on Berlin timezone under WP Settings > General. Minesh asked us to try changing to UTC or UTC +2 but I didn't notice any difference.

The change to wp_date was useful for calculating the [days-before-expiry] short code and I found another shortcode on Toolset support forum to add [now], which allows us to display the UNIX number on the front end usefully for testing purposes.

When I use the conditional expresssions below to display on the front end for testing, it puts the post in the correct group

[wpv-conditional if="( '[days_before_expiry]' lte '0' )"]This post has expired.[/wpv-conditional]
[wpv-conditional if="( '[days_before_expiry]' gt '0' ) AND ( '[days_before_expiry]' lte '3600' )"]Expiring in next 1 hour (3600 seconds).[/wpv-conditional]
[wpv-conditional if="( '[days_before_expiry]' gt '3600' ) "]Not expiring in next 1 hour.[/wpv-conditional]

So it looks like the [days-before-expiry] appears to be calculating correctly. ✅

However the Query view filter does not appear to be obeying filters using the standard NOW() function. To test this, I changed the Post Form New Job Ad to set an expiry timestamp of one hour after creation for test purposes, so 3600 seconds.
(1) I set the Query Filter for Expiring Job Ads to _cred_post_expiration_time is a number between NOW(), SECONDS_FROM_NOW(3600) >> ie show Job Ads expiring in next hour.
(2) I set the Query Filter for Expired Job Ads to _cred_post_expiration_time is a number lower than or equal NOW() >> show expired Job Ads

The two views are not showing the expected results.

To test it, you could create a new Job Ad using login nativefamily6 here: hidden link

Then, theoretically it should appear in the view Expiring Job Ads view hidden link, but it doesn't.

Kind regards
Simon