FlexibleSearch query to fetch one-day old records

Question

Is there a way to do flexiblesearch query for fetching one-day old records?

Something like:

select * from {table} where {conditions}

where {conditions} are old-day old records?

Answer 1

Something like this should work:

SELECT * FROM {Product} WHERE {creationTime} < NOW() - INTERVAL 1 DAY

Instead of creationTime, you could also use modifiedTime, depending on what you want.

Answer 2

Every itemtype has an attribute called creationtime which stores the timestamp when a record of this itemtype is created in the database. Similarly, there is another attribute called modifiedtime which stores the timestamp when the record is modified in the database. You can use one of these attributes as per your requirement e.g.

1. The query to find the products which are older than 1 day:

   SELECT {pk} FROM {Product} WHERE {creationtime} < DATE_SUB(NOW(), INTERVAL 1 DAY)
   

2. The query to find the products which are exactly 1 day old:

   SELECT {pk} FROM {Product} WHERE {creationtime} = DATE_SUB(NOW(), INTERVAL 1 DAY)
   

Check ?hybris? ? ?bin? ? ?platform? ? ?ext? ? ?core? ? ?resources ? core-items.xml? to know more about all the attributes of the itemtype code="Item" which is the supertype of all the itemtypes and hence every itemtype inherits all of its attributes by default.

Answer 3

Instead of relying on the DB function(DATE_SUB, INTERVAL ), I would rather calculate the date in java and pass the Date object to Flexible query like this

final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery("
SELECT * FROM TABLE AS t WHERE t.creationtime < ?inputDate")

flexibleSearchQuery.addQueryParameter("inputDate", 
                              ZonedDateTime.now(ZoneId.systemDefault()).toInstant().minus(1, ChronoUnit.DAYS));

Answer 4

There condition could be on 2 columns:

1. {modifiedtime}
2. {creationtime}

FlexQueries:-

 SELECT * FROM {product} WHERE {creationtime} < current_date - interval 1 day 

 SELECT * FROM {product} WHERE {modifiedtime} < current_date - interval 1 day

for more than 1 day:

SELECT * FROM {product} WHERE {modifiedtime} < current_date - interval 10 day