Find all entities with dates between, spring data jpa

user_10314123 0
Viewed 32

I have this entity:

public class Tender {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(nullable = false, updatable = false)
private Long id;
private String source;
private String sourceRefNumber;
private String link;
private String title;
@Column(columnDefinition="TEXT")
private String description;
private String field;
private String client;
private String type;
private Date date;
private Date deadline;
@ManyToMany
private List<Cpv> cpv;}

And for this following interface:

public interface TenderRepository extends JpaRepository<Tender, Long> {

List<Tender> findAllByDateGreaterThanEqualAndDateLessThanEqual(Date start, Date end);

List<Tender> findAllByDateBetween(Date start, Date end);}

I use this interface in my Service and ServiceImpl. In my Resource I have method:

@GetMapping("/listforperiod/{start}/{end}")
public ResponseEntity<List<Tender>> getTendersForPeriod(@PathVariable("start")
                                                        @DateTimeFormat(pattern = "yyyy-MM-dd") Date start,
                                                        @PathVariable("end")
                                                        @DateTimeFormat(pattern = "yyyy-MM-dd") Date end) {
    List<Tender> list = tenderService.findAllByDateBetween(start, end);
    return new ResponseEntity<>(list, OK);}

I have tried both findAllByDateBetween and findAllByDateGreaterThanEqualAndDateLessThanEqual - both return 0 records, but in database I see all of them: enter image description here

Where can be a problem?

jpa date spring
1 Answers

Looks like the reason is a time zone. MySQL driver uses incorrect time zone transformations, using a default local time zone in place of a connection time zone (or vice versa).

You can debug this query inside MySQL driver. MySQL driver uses Calendar with default parameters, which means using a time zone from your computer.

Another reason can be that you use Date fields in the entity. Date can be converted itself, using default time zone.

You can add parameters to the database URL to see which actual values are passed for the prepare statement

jdbc:mysql://<DATABASE_URL>?logger=com.mysql.cj.log.Slf4JLogger&profileSQL=true

Also you can use JPQL with hardcoded date values and see what happens.

Notes

Always store data in the database in UTC time zone. And use Instant for date fields in the entity. You can try Instant and UTC and, probably, the issue will disappear.

user_34051710
Related