AEM Query | Pull Articles by comparing date difference of 14 days

[ssharma48]

Hi,

We have a requirement where we need to pull out the list of articles for which the jcr:created and PublishDate (a custom field on page property) has a difference of 14days.

For example, if create Date is 16th Sept and PublishDate is 31st Aug, then this article should not come up in the list.

Whereas if create Date is 10th Sept and PublishDate(manually authored) is 31st Aug, then this article should come up in the list.

I tried using the dataRange and relativeDateRange but didn't work.

Any pointer will be helpful.

Thanks,

Shikha

[Sady_Rifat · Accepted solution]

Hello @ssharma48 ,

QueryBuilder is the tool for searching and filtering content in AEM, you may need to combine it with custom Java code that involves date calculations. This is because QueryBuilder is primarily designed for selecting nodes based on their properties and structure, not for performing calculations on property values.

for (Hit hit : result.getHits()) {
    Resource resource = hit.getResource();
    ValueMap properties = resource.getValueMap();
    Calendar jcrCreated = properties.get("jcr:created", Calendar.class);
    Calendar publishDate = properties.get("PublishDate", Calendar.class);

    if (jcrCreated != null && publishDate != null) {
        long timeDifference = publishDate.getTimeInMillis() - jcrCreated.getTimeInMillis();
        long days = TimeUnit.MILLISECONDS.toDays(timeDifference);
        if (days >= 14) {
            // add to your page list
        }
    }
}

[aanchal-sikka]

Hello @ssharma48 

We cannot compare 2 dates. 

Option-1:

you can combine the PublishDate and relativeDateRange(for jcr:created) to get the results, using PublishDate as focal point and jcr:created 14 days from this.

Option-2:

It depends on what your implementation is. Else, try tree traversal, if sub-tree is small and pagination is not needed. It can also be used, if results can be generated on regular interval and can be cached.

If its a one-time report generation, please use Groovy script and then you can traverse the entire tree and generate report.

[ssharma48]

Thank you both. Will try.

Regards,

Shikha

[AshwiniSathe1]

@ssharma48 What is the type for the custom property? Date format should be same to compare or get difference between two dates. Please check the date format and use same format before calculating the difference.

Please share the code snippet or jcr property screenshot.

[Suraj_Kamdi]

@ssharma48 
Refer Relative date range concept from query builder API

You can specify lowerBound and upperBound using either a millisecond value or use the syntax as 1s 2m 3h 4d 5w 6M 7y

Reference

https://experienceleague.adobe.com/docs/experience-manager-65/developing/platform/query-builder/quer...

P.S. I am considering comparing it with the current date.

[kautuk_sahni]

@ssharma48 Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.