Spring JPA Query to Find the Offered Price on the Current Date by Searching Between a Range of Two Dates

To find the price based on the current date by searching between a "from" and "to" date, you can do the following:

Suppose you have an entity named ProductPrice with fields fromDate, toDate, and value, representing the price over a specific period:

import java.util.Date;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "product_price")
public class ProductPrice {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private String id;

  @Column(name = "price")
  private double price;

  @Column(name = "from_date")
  private Date fromDate;

  @Column(name = "to_date")
  private Date toDate;

}

Query method to find the offered price on the current date by searching between a range of two dates:

import java.util.Optional;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.price.entity.ProductPrice;

@Repository
public interface ProductPriceRepository extends CrudRepository<ProductPrice, Long> {

  @Query("SELECT pp.price FROM ProductPrice pp WHERE pp.fromDate <= CURRENT_DATE AND pp.toDate >= CURRENT_DATE")
  Optional<Double> findOfferedPriceForCurrentDate();

}

Here's another example of a JPA query method to find the offered price between the start and the end date based on the current date:

import java.util.Date;
import java.util.Optional;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.price.entity.ProductPrice;

@Repository
public interface ProductPriceRepository extends CrudRepository<ProductPrice, Long> {

  Optional<ProductPrice> findByFromDateLessThanEqualAndToDateGreaterThanEqual(Date currentDate1,
      Date currentDate2);

}

Now, in your service class, you can use the repository method to find the appropriate price object:

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Optional;
import java.util.TimeZone;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.price.entity.ProductPrice;
import com.example.price.repository.ProductPriceRepository;
import com.example.price.service.PriceService;

@Service
public class PriceServiceImpl implements PriceService {

  @Autowired
  private ProductPriceRepository productPriceRepository;

  @Override
  public Double getOfferedPrice() {
    
    Date serverCurrentDate = new Date();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    
    // setting time zone of your database
    dateFormat.setTimeZone(TimeZone.getTimeZone("UTC"));

    String currentDateStr = dateFormat.format(serverCurrentDate);

    Date currentDate = null;

    try {
      currentDate = dateFormat.parse(currentDateStr);
    } catch (ParseException e) {
      e.printStackTrace();
    }
    
    Optional<ProductPrice> productPriceOpt =
        productPriceRepository.findByFromDateLessThanEqualAndToDateGreaterThanEqual(currentDate, currentDate);
    if (productPriceOpt.isPresent()) {
      // return discount price
      return productPriceOpt.get().getPrice();
    }
    
    // should return original price 
    return null;
  }

}