Implement search with date range

I want to implement Date range search using Spring Boot. I tried this:

url: localhost:8080/users/users?page=0&size=3&createdAt=2023-05-02T04:57:19.83795Z

Endpoint:

    @GetMapping(value = "/users", produces = MediaType.APPLICATION_JSON_VALUE)
    public Page<UsersResource> getUsersBySearchSpecification(@Valid UserSearchParams params, Pageable pageable)
    {
        Page<UsersResource> list = userService.getUsersBySpecification(params, pageable);
        return list;
    }

UserSearchParams:

@Getter
@Setter
public class UserSearchParams {

    private String param1;

    private Integer param2;

    private List<String> param3;

    @DateTimeFormat(iso = DATE_TIME) 
    LocalDateTime startDate;

    private OffsetDateTime createdAt;
}

Search functionality:

    public Page<Users> getUsersBySpecification(UserSearchParams params, Pageable pageable)
    {
        Specification<Users> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (params.getCreatedAt() != null) {
                predicates.add(cb.greaterThan(root.get("createdAt"), params.getCreatedAt()));
            }
            if (params.getCreatedAt() != null) {
                predicates.add(cb.lessThan(root.get("createdAt"), params.getCreatedAt()));
            }
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };

    return userRepository.findAll(spec, pageable);
}

The search functionality is not working. What data type should I use for createdAt? OffsetDateTime or LocalDateTime is better alternative?

Should I manually parse the value 2023-05-02T04:57:19.83795Z or I can rely on Spring Boot to parse the date?.

Is this code properly implemented:

        if (params.getCreatedAt() != null) {
            predicates.add(cb.greaterThan(root.get("createdAt"), params.getCreatedAt()));
        }
        if (params.getCreatedAt() != null) {
            predicates.add(cb.lessThan(root.get("createdAt"), params.getCreatedAt()));
        }

Or there is better way to to have this range select?

Let me answer step by ste based on the experience that I have (multiple different enterprise projects). Additional note: I was mostly working with PostgreSQL as a database.

• Usually LocalDateTime is used. Because it's a bit more convenient to work with an it has no time-zone info. For databases usually timestamp is the appropriate column type.

• Spring Boot is able to parse request parameters to LocalDateTime type without explicit custom config implementations. Here is an example of request for LocalDateTime type:

  • https://localhost:8080/my/endpoint?startDate=2023-01-01T00:30:00.000&endDate=2023-12-31T23:30:00.000
  • https://localhost:8080/my/endpoint?startDate=2023-01-01T00%3A30%3A00.000&endDate=2023-12-31T23%3A30%3A00.000 (html decoded)

  Here is the appropriate controller code:

    @GetMapping(value = "/my/endpoint")
    public void myMethod(
            @RequestParam(name = "startDate") @DateTimeFormat(iso = DATE_TIME) LocalDateTime startDate,
            @RequestParam(name = "endDate") @DateTimeFormat(iso = DATE_TIME) LocalDateTime endDate) {
        return doSearch(startDate, endDate);
    }

But be careful with old Spring versions. From my experience, it should work since Spring Boot 2.2.X. The idea is to have an implementation of JPA higher than 2.1 version, because in 2.1 version there is no convenient using of LocalDateTime. Simply put, use modern versions.

A bit more information can be found here.

• As for implementation. Using LocalDateTime and JPA implementation 2.2 or higher, your code should work. You can also check code samples here:

  • https://dzone.com/articles/dealing-with-javas-localdatetime-in-jpa
  • enter link description here (it's about LocalDate, but should work with LocalDateTime too.