Query Records Between Two Dates With Hibernate

1. Overview

Querying data within a specific time range is a core requirement for most enterprise apps. Whether we’re generating monthly financial statements or filtering logs from the last 24 hours, Hibernate provides several ways to handle these temporal queries.

In this tutorial, we’ll explore how to query records between two dates using HQL, the Criteria API, and Native SQL.

2. Setup

To demonstrate these operations, let’s define an Order entity. While older versions of Hibernate required specific annotations for dates, modern versions (Hibernate 5+) handle Java 8 java.time types natively:

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String trackingNumber;

    private LocalDateTime creationDate;

    // Getters and Setters
}

If we’re still using the legacy java.util.Date, we’d need to use the @Temporal annotation to specify whether we want to store just the date, the time, or both:

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

3. Using Hibernate Query Language (HQL)

HQL is the most common way to write date-range queries in Hibernate. It’s readable, portable across databases, and lets us work with our entity model rather than raw SQL tables.

3.1. Using the BETWEEN Keyword

The BETWEEN operator is the most straightforward way to query records within a specific range. It is inclusive on both ends, meaning a record that falls exactly on the startDate or the endDate will be included in our results:

String hql = "FROM Order o WHERE o.creationDate BETWEEN :startDate AND :endDate";
List<Order> orders = session.createQuery(hql, Order.class)
  .setParameter("startDate", startDate)
  .setParameter("endDate", endDate)
  .getResultList();

While this syntax is clean, it introduces a common logical pitfall when working with LocalDateTime. If we intend to capture all orders for January 31st but provide an endDate of 2024-01-31 00:00:00, the query will exclude almost the entire day. Because the operator is inclusive only up to that exact midnight boundary, an order placed at 10:30 AM or 9:00 PM on the 31st is technically “greater than” the end parameter and will be omitted from the results.

To capture the full final day using BETWEEN, we would be forced to manually set the time to the very last millisecond of the day (23:59:59.999). To avoid this fragile manual calculation, a more robust pattern is to use comparison operators to create a half-open interval.

3.2. Using Comparison Operators

When we’re querying calendar boundaries like full days or months, the safest pattern is to use a half-open interval: inclusive on the lower bound and exclusive on the upper bound. This means we use >= for the start and < for the end.

Let’s say we want all orders from January 2024. Instead of trying to calculate January 31st at 11:59:59 PM, we can simply use February 1st as our exclusive endDate:

LocalDateTime startDate = LocalDateTime.of(2024, 1, 1, 0, 0, 0);
LocalDateTime endDate = LocalDateTime.of(2024, 2, 1, 0, 0, 0); // exclusive

String hql = "FROM Order o WHERE o.creationDate >= :startDate " +
  "AND o.creationDate < :endDate";

List<Order> orders = session.createQuery(hql, Order.class)
  .setParameter("startDate", startDate)
  .setParameter("endDate", endDate)
  .getResultList();

This pattern is preferred because it works correctly regardless of whether our database stores microseconds, milliseconds, or seconds. We never have to worry about missing records that fall on the boundary.

4. Using the Criteria API

The Criteria API provides a programmatic way to build date-range queries. It’s particularly valuable when we’re building dynamic search screens where the user might provide a start date, an end date, both, or neither. Unlike HQL strings, the Criteria API is typesafe, so our IDE can help us refactor field names when they change.

4.1. Basic Query with BETWEEN

For simple inclusive queries, we can use the between() method. Just like with HQL, this includes records that fall exactly on the endDate:

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Order> query = cb.createQuery(Order.class);
Root<Order> root = query.from(Order.class);

query.select(root)
  .where(cb.between(root.get("creationDate"), startDate, endDate));

List<Order> orders = session.createQuery(query).getResultList();

4.2. Using Comparison Operators

When we need an exclusive upper bound, we replace between() with greaterThanOrEqualTo() and lessThan(). This gives us the same half-open interval pattern we saw in HQL:

Predicate startPredicate = cb
  .greaterThanOrEqualTo(root.get("creationDate"), startDate);
Predicate endPredicate = cb
  .lessThan(root.get("creationDate"), endDate);

query.select(root)
  .where(cb.and(startPredicate, endPredicate));

4.3. Building Dynamic Queries

The Criteria API truly shines when some filters are optional. We can build a list of Predicate objects and apply only the ones the user provided:

List<Predicate> predicates = new ArrayList<>();

if (startDate != null) {
    predicates.add(cb.greaterThanOrEqualTo(root.get("creationDate"), startDate));
}
if (endDate != null) {
    predicates.add(cb.lessThan(root.get("creationDate"), endDate));
}

query.select(root).where(predicates.toArray(new Predicate[0]));

This approach keeps our code clean and avoids the string concatenation headaches we’d face with dynamic HQL.

5. Using Native SQL

Sometimes we need to use database-specific date functions that HQL doesn’t support, for example, PostgreSQL’s DATE_TRUNC or Oracle’s TRUNC. In these cases, Hibernate lets us fall back to native SQL queries.

Here’s how we write our date-range query directly in SQL. Notice that we use database column names like creation_date rather than entity field names like creationDate:

String sql = "SELECT * FROM orders WHERE creation_date >= :startDate " +
  "AND creation_date < :endDate";

List<Order> orders = session.createNativeQuery(sql, Order.class)
  .setParameter("startDate", startDate)
  .setParameter("endDate", endDate)
  .getResultList();

For more complex scenarios, we can leverage specific database features. For example, if we want to ignore the time component entirely, we can use database-specific functions like PostgreSQL’s DATE_TRUNC or MySQL’s DATE():

// PostgreSQL
String sql = "SELECT * FROM orders WHERE DATE_TRUNC('day', creation_date) >= :startDate " +
  "AND DATE_TRUNC('day', creation_date) < :endDate";

// MySQL
String sql = "SELECT * FROM orders WHERE DATE(creation_date) >= :startDate " +
  "AND DATE(creation_date) < :endDate";

While native SQL is powerful, we should use it sparingly. Every native query we write ties our application to a specific database. If we ever switch from PostgreSQL to MySQL, we’ll need to rewrite these queries. As a rule of thumb, start with HQL and only reach for native SQL when Hibernate’s HQL simply can’t express what we need.

6. Conclusion

We’ve covered three distinct ways to query records between two dates in Hibernate. HQL is the best choice for most scenarios thanks to its readability and portability. The Criteria API shines when building dynamic queries with optional filters. Native SQL serves as a powerful fallback for database-specific features, though we should be mindful of the vendor lock-in it creates.

For most applications, sticking with the half-open interval pattern (>= startDate AND < endDate) in HQL will be the simplest and most reliable approach.

As always, the complete source code for the tutorial is available over on GitHub.