AEM cloud Date Range Predicate problem

[Lakshmi9]

Hi Team, 

Kindly suggest me on the below.

Written a query to list out all pages which are available in a period of time. 

startdate: 2022-09-16T01:05:00.000+05:30 Issue here is the content started showing at 1:06. It supposed to be shown at 1:05 as mentioned in the startdate. 1 min delay in picking up the start date.
expirydate : 2022-09-17T23:59:00.000+05:30 There is no issue with the end date.

path=/content/xyz
type=cq:PageContent
1_daterange.property=2022-09-16T01:05:00.000+05:30  -- start date
1_daterange.upperBound=2022-09-16T01:04 -- currentdate
2_daterange.property=2022-09-17T23:59:00.000+05:30 -- end date
2_daterange.lowerBound=2022-09-16T01:04 -- currentdate

Thank you!!

[poojac35931336]

You can add  lowerOperation property to include the 1:05 as well 

Can you check the query you have shared? doesn't seem proper.

[Jagadeesh_Prakash · Accepted solution]

Hi @Lakshmi9 Hope below queries will be useful. Below examples are for DAM assets but you can use it for cq:Pages and try. Below examples worked for us accuratly in AEM Cloud 

• If we want to get the assets modified after a particular date, we can use the query as below:

 SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "<path of assets to be traversed>") AND p.[jcr:content/jcr:lastModified] >= CAST("<date>" AS DATE)

 For example: SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "/content/dam") AND p.[jcr:content/jcr:lastModified] >= CAST("2022-04-01T10:36:00.000-05:00" AS DATE)

• If we want to get the assets modified in between a particular date range, we can use the query as below:

 SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, “"<path of assets to be traversed>") AND p.[jcr:content/jcr:lastModified] >= CAST("<From date>" AS DATE) AND p.[jcr:content/jcr:lastModified] <= CAST(“<To date>” AS DATE)

For example: SELECT * FROM [dam:Asset] AS p WHERE ISDESCENDANTNODE(p, "/content/dam") AND p.[jcr:content/jcr:lastModified] >= CAST("2022-03-01T10:36:00.000-05:00" AS DATE) AND p.[jcr:content/jcr:lastModified] <= CAST("2022-04-20T23:59:59.999Z" AS DATE)

• If we want to get the tags created after a particular date we can use query as below:
  SELECT * FROM [cq:Tag] AS p WHERE ISDESCENDANTNODE(p, "/content/cq:tags") AND p.[jcr:created] >= CAST("2018-04-01T10:36:00.000-05:00" AS DATE)