How to fetch an entity having a timestamp property that is within an hour from other timestamp property with JPA and Hibernate

Question

I have a doubt in JPA query criteria builder with path or expression.

I want to find who are all entered first post comment with in an hour after their  account created

Path<Date> accountCreatedTime = root.<Date> get("AccountCreatedTime");
Path<Date> firstPostCreatedTime = root.<Date> get("FirstPostCreatedTime");

final Predicate timeInHourPredicate = criteriaBuilder
    .greaterThanOrEqualTo(accountCreatedTime, FirstPostCreatedTime);

Example:

1. Account Created at: 2018-SEP-10 at 10am and First Post entered 2018-SEP-10 at 10.15 am  this recond should be fetched. (FALLS IN AN HOUR)

2. Account Created at: 2018-SEP-10 at 10am and First Post entered 2018-SEP-10 at 3.50 pm  this SHOULD NOT  be fetched.

Is there any  way to add or separate hours from Path accountCreatedTime? or can we get difference between   Path accountCreatedTime  and Path FirstPostCreatedTime  in hours and in criteriaBuilder

Answer 1

Using DATEDIFF

The SQL would be like this:

select u
from users u
join posts p on p.user_id = u.id
where datediff('hour', u.account_created_time, p.first_post_created_time)

For more details about how you can register the DATEDIFF function with Criteria API, check out this article.