Springboot data-jpa findByDate()

Question

I'm trying to retrieve Today's records from mysql database.

I tried using springboot data-jpa and the findByDate(Date date, Pageable pageable) method from jpa-repository and it always return empty results.

Here's the created Entity code :

@Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_synop")
    private Long id;

    @Column(name="contenu")
    private String contenu;

    @Column(name="date", columnDefinition="TIMESTAMP(0)")
    @Temporal(TemporalType.TIMESTAMP)
    private Date date;

    @ManyToOne(cascade= {CascadeType.DETACH,CascadeType.MERGE,CascadeType.PERSIST,CascadeType.REFRESH},
            fetch=FetchType.LAZY)
    @JoinColumn(name="id_station")
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
    private Station station;

and here's the Mysql table created with Mysql WorkBench :

CREATE TABLE `synop` (
  `id_synop` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_station` bigint(20) NOT NULL,
  `contenu` varchar(255) NOT NULL,
  `date` timestamp NOT NULL,
  PRIMARY KEY (`id_synop`),
  UNIQUE KEY `idmsgsynop_UNIQUE` (`id_synop`),
  KEY `fk_synope_station_idx` (`id_station`),
  CONSTRAINT `fk_synope_station` FOREIGN KEY (`id_station`) REFERENCES `station` (`id_station`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

The jpa repository Code :

import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface SynopRepository extends JpaRepository<Synop, Long> {
    public Page<Synop> findByStationId(Long stationId, Pageable pageable);

    public Page<Synop> findByDate(Date date, Pageable pageable);
}

and the controller code :

@GetMapping("/synops-today")
    public Page<Synop> findToday(@RequestParam(defaultValue="1") int page,Pageable pageable) {
        return synopRepository.findByDate(new Date(),PageRequest.of(page-1, 10, Sort.by("date").ascending()));
    }

I expect to get today's records by using the findByDate(...) method, but it doesn't seem to work. I also noticed a little problem when viewing data from MysqlWorkbench : Dates seem to be 2h in advance (ex : now it's 11:57 but in mysql workbench software it shows 9:57, when i query the server time it shows the irght tme though...) I actually don't care about the hours/mins/secs i just want to retrieve today's records.

are you using mysql dialact ? `spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect` — Milan Desai, May 24 '19 at 11:04
Try to change the date from java.util.date to sql.date in your cntrlr... — Porkko M, May 24 '19 at 11:06
try #findByDateBetween(Date start, Date end, Pageable pageable), start is today's date plus 00:00:00, end is today's date plus 24:00:00. "Dates seem to be 2h in advance", maybe it's a time zone problem. — Allen Kerr, May 24 '19 at 11:14

score 1 · Accepted Answer · answered May 25 '19 at 12:49

I found a working solution for my problem, apparently when using the TIMESTAMP data type the JPA-Data "findByDate()" method doesn't work out of the box. I actually needed to find records by comparing the date part of the timestamp only so i used the query annotation like this :

@Query("from Synop s where DATE(s.date) = :date")
public Page<Synop> findByDate(@Param("date") Date date, Pageable pageable);

it's the sql DATE() function that helped here since it only takes the date part of the timestamp into account when comparing it with date i provided for the search. With this i could search for data comparing the dates while keeping the timestamp type (to display hours,mins,secs..) Thanks for the help.

Punit Tiwan · Answer 2 · 2019-05-24T11:40:57.333

0

Try if this solution help you -> date-only-comparison-without-time-using-jpa-named-query

Basically, try using @Temporal(TemporalType.DATE) instead of @Temporal(TemporalType.TIMESTAMP)

If not then you can write custom query which uses sql funtion to match only date part and not time -> comparing-dates-in-mysql-ignoring-time-portion

edited May 24 '19 at 11:40

answered May 24 '19 at 11:23

Punit Tiwan

106
5

Your solution works but i need the timestamp data type so that i can display hours of creation. If i change it to DATE it'll give me 00:00:00 hours for all my records. – user7616817 May 25 '19 at 12:42
The link that I gave as a second solution is by using “Date” function. You can use that to keep the time detail. – Punit Tiwan May 26 '19 at 16:15

score 0 · Answer 3 · answered May 24 '19 at 12:03

I think, you should switch from java.util.Date to java.sql.Date at all places. There are methods to convert among the two.

Though I prefers between logic than doing findByDate. Between logic is going to be more accurate.

java.sql.Date JavaDoc ,

A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT.

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

java.util.Date JavaDoc - this JavaDoc is long but relevant section,

The class Date represents a specific instant in time, with millisecond precision.

Springboot data-jpa findByDate()

3 Answers3