Fetch Last 24 hour data using Gemfire OQL

Question

Can anyone help me with fecthing exactly last 24 hr data directly using gemfire OQL or some custom java code.

i tried using LIKE query and fetch two days of data but i am not sure how two pass dynamic data in gemfire query since everything is treated as string.

Answer 1

If you require data for a specific time frame, you will have to add a field which will the contain the date/data you want to query against.

In your case you want to query some time parameter. Best way to store this, would be a long field that would represent the Date as a long. Then your query would require a simple >= and < comparison, which are quick, because they aren't String comparisons.

String query = "SELECT * from /DataRegion r where r.createdDate >= $1 AND r.createdDate < $2";

Then your query becomes simpler, as all you'd have to do is determine the start and end date in terms of its long representation. new Date(2023,03,10).getTime() (of course you should use the java.util.Calender class, but this is just for example purposes).

Then using the bind logic in GemFire, you can then run the query.

Query query = queryService.newQuery(queryString);

// set a query bind parameter
Object[] params = new Object[2];
params[0] = new Date(2023,03,10).getTime();
params[1] = new Date(2023,03,12).getTime();

// Execute the query locally. It returns the results set.
SelectResults results = (SelectResults) query.execute(params);

Answer 2

@Udo is correct, here!

Storing or exposing some timestamp (long or `Long' value) on your application domain object(s), that are stored and managed in VMware GemFire, is actually preferable in this case.

It could even look something like this...

package example.app.customers.model;

import java.time.Instant
...
@Region("Customers")
class Customer {

  private Long id;

  private Instant joined;

  private final String name;

  public long getJoinedTimestamp() {

    Instant localJoined = this.joined;

    return localJoined != null ? localJoined.toEpochMilli() : 0L;
  }

  // other getters, setters and accessors omitted

}

However, I would NOT use java.util.Calendar, but rather use the (@since) Java 8 date/time types in the java.time package (Javadoc), like java.time.Instant (Javadoc).

Then, it would also be a simple matter to write a derived query using the Spring Data (SD) [GemFire] Repository abstraction, too!

There is no need to use GemFire's OQL-based query API (:P) in this case!

So, you could have SD GemFire Repository declaration like the following:

package example.app.customers.repo;

interface CustomerRepository extends CrudRepository<Customer, Long> {

  List<Customer> findByJoinedTimestampGreaterThanEqualAndJoinedTimestampLessThan(Long begin, Long end);

NOTE: The time range could be inclusive, too, such as findByJoinedTimestampGreaterThanEqualAndJoinedTimestampLessThanEqual(..) as well.

NOTE: In my example above, the joined timestamp is inclusive on the lower bound and exclusive on the upper bound.

You can find out more about Spring Data GemFire's (SDG) extension of Spring Data's (SD) Repository infrastructure and querying capabilities here, in the SDG doc, and specifically on SDG derived (OQL) queries in doc as well.

For more general information on the Spring Data Repository infrastructure abstraction, see the Spring Data Commons' documentation, and specifically about query methods.

Good luck!

Answer 3

Check out the query docs: https://docs.vmware.com/en/VMware-GemFire/10.0/gf/getting_started-querying_quick_reference.html

Here's an example of how to use bind parameters.

// specify the  query string
 String queryString = "SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = $1";

QueryService queryService = cache.getQueryService();
Query query = queryService.newQuery(queryString);

// set a query bind parameter
Object[] params = new Object[1];
params[0] = "active";

// Execute the query locally. It returns the results set.
SelectResults results = (SelectResults) query.execute(params);