How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

I am struggling with such a simple task and I have no idea what I am doing wrong.

Based on personal research, it seems I am coding what I am supposed to in order to search all records between two dates using Spring Data. As far as I understood, "@Temporal(TemporalType.DATE)" does the "magic".

Repository

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.persistence.TemporalType;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.Temporal;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.mybank.accountmanagement.model.Transaction;

    @Repository
    public interface TransactionRepository extends JpaRepository<Transaction, Long> {
    
    	
@Query(value = "SELECT * FROM transactions WHERE account_id=:idAccount AND CAST(created_at AS date) BETWEEN :fromDate AND :toDate ", nativeQuery = true)
List<Transaction> findByAccountIdWithCreationDateBetween(@Param("idAccount") Long idAccount,
		@Param("fromDate") @Temporal(TemporalType.DATE) Date fromDate,
		@Param("toDate") @Temporal(TemporalType.DATE) Date toDate);
    	
    }

Models:

Transaction

package com.mybank.accountmanagement.model;

import java.math.BigDecimal;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;

import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;
import org.springframework.format.annotation.NumberFormat;

import com.fasterxml.jackson.annotation.JsonIdentityInfo;
import com.fasterxml.jackson.annotation.JsonIdentityReference;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.ObjectIdGenerators;

@Entity
@Table(name = "transactions")
public class Transaction  extends AuditModel {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@ManyToOne(fetch = FetchType.LAZY, optional = false)
	@JoinColumn(name = "account_id", nullable = false)
	@OnDelete(action = OnDeleteAction.CASCADE)
	@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
	@JsonIdentityReference(alwaysAsId = true)
	@JsonProperty("accoout_id")
	private Account account;

	@NotNull
	@NumberFormat(pattern = "#,###,###,###.##")
	private BigDecimal amount;

	@NotNull
	private int transactionType;

	
	
	
	public Transaction(Account account, @NotNull BigDecimal amount, @NotNull int transactionType) {
		super();
		this.account = account;
		this.amount = amount;
		this.transactionType = transactionType;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Account getAccount() {
		return account;
	}

	public void setAccount(Account account) {
		this.account = account;
	}

	public BigDecimal getAmount() {
		return amount;
	}

	public void setAmount(BigDecimal amount) {
		this.amount = amount;
	}

	public int getTransactionType() {
		return transactionType;
	}

	public void setTransactionType(int transactionType) {
		this.transactionType = transactionType;
	}

	
}

AuditModel

package com.mybank.accountmanagement.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = { "createdAt", "updatedAt" }, allowGetters = true)
public abstract class AuditModel implements Serializable {
	@Temporal(TemporalType.TIMESTAMP)
	@Column(name = "created_at", nullable = false, updatable = false)
	@CreatedDate
	private Date createdAt;

	@Temporal(TemporalType.TIMESTAMP)
	@Column(name = "updated_at", nullable = false)
	@LastModifiedDate
	private Date updatedAt;

	public Date getCreatedAt() {
		return createdAt;
	}

	public void setCreatedAt(Date createdAt) {
		this.createdAt = createdAt;
	}

	public Date getUpdatedAt() {
		return updatedAt;
	}

	public void setUpdatedAt(Date updatedAt) {
		this.updatedAt = updatedAt;
	}
}

What I was expcting to return when searching with "fromDate":"2020-08-02", "toDate":"2020-08-02"

But it is returning no record at all

*** Edited

For some unknown reason for me, my records with exact date matching fromDate and toDate are token off from result

1. Searching with longer fromDate and toDate

   curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{
   "fromDate":"2020-08-01",
   "toDate":"2020-08-03"
   }'

brings two records:

[
    {
        "createdAt": "2020-08-02T16:29:08.085+00:00",
        "updatedAt": "2020-08-02T16:29:08.085+00:00",
        "id": 1,
        "amount": 1.00,
        "transactionType": 2,
        "accoout_id": 1
    },
    {
        "createdAt": "2020-08-02T16:29:11.185+00:00",
        "updatedAt": "2020-08-02T16:29:11.185+00:00",
        "id": 2,
        "amount": 2.00,
        "transactionType": 1,
        "accoout_id": 1
    }
]

so far so good

2. now I get a problem while searching with exact date. I would expect same result from above query

   curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{
   "fromDate":"2020-08-02",
   "toDate":"2020-08-02"
   }'

brings no result at all

*** edited. Thanks to sample provided by Kavithakaran Kanapathippillai, I noticed that it works with new Date or 2020-08-02T00:00:00.000+00:00 but it fails with 2020-08-02. I am still lost why it fails with 2020-08-02 since I don't care about time and also it works if try one day less: 2020-08-01

Controller

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.mybank.accountmanagement.BankStatementFilter;

import com.mybank.accountmanagement.model.Transaction;
import com.mybank.accountmanagement.repository.TransactionRepository;
import com.mybank.accountmanagement.service.TransactionService;

@RestController
public class TransactionController {

	@Autowired
	TransactionService transactionService;

	@Autowired
	TransactionRepository transactionRepository;

	@GetMapping("/transaction/{accountId}/bankstatement")
	public List<Transaction> bankStatement(@PathVariable(value = "accountId") Long accountId,
			@Valid @RequestBody BankStatementFilter bankStatementFilter) {

		return transactionRepository.findByAccountIdWithCreationDateBetween(accountId,
				bankStatementFilter.getFromDate(), bankStatementFilter.getToDate());
	}

}

A simple Pojo used only to get new Date from the string sent by client (ex. Postman or curl)

import java.util.Date;

public class BankStatementFilter {
	
	private Date fromDate;
	private Date toDate;
	public Date getFromDate() {
		return fromDate;
	}
	public void setFromDate(Date fromDate) {
		this.fromDate = fromDate;
	}
	public Date getToDate() {
		return toDate;
	}
	public void setToDate(Date toDate) {
		this.toDate = toDate;
	}

}

It smells some stupid thing done from my part with this POJO BankStatementFilter

Here is what I noted while comparing new Date() with 2020-08-02. Since I don't care about time, isn't that correctly?

*** Final comment

It is working now. I changed my POJO to bellow. If I am doing some silly thing I will be thanks for advice. BTW, my original question was 100% answered.

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class BankStatementFilter {

	private Date fromDate;
	private Date toDate;

	public Date getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {

		String pattern = "yyyy-MM-dd";
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);

		try {
			this.fromDate = simpleDateFormat.parse(fromDate);
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}

	public Date getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		String pattern = "yyyy-MM-dd";
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);

		try {
			this.toDate = simpleDateFormat.parse(toDate);
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}

}

Have you tried using >= and <= in the SQL query, in my case that works as expected.

Also my advice would be to not use native query and try using model class for quick and correct Deserialization in your case since you have the model class defined.

• @Temporal.Date does not have any impact on your spring data repository method. It has impact only when it is annotated in the entity.

• Instead do the following to cast your parameters too:

    @Query(value = "SELECT * FROM transactions WHERE account_id=:idAccount " +
            "AND CAST(created_at AS date) " +
            "BETWEEN CAST(:fromDate AS date) AND CAST(:toDate AS date)"
            , nativeQuery = true)
    List<Transaction> findBy(@Param("idAccount") Long idAccount,
                             @Param("fromDate") Date fromDate,
                             @Param("toDate") Date toDate);

• Here is sample project which I set up with your entity except account as it is not in the question if you like to compare. You can import the project as maven into IntelliJ and run the main application, it will insert data and bring it back

  https://github.com/kavi-kanap/stackoverflow-63212441