Spring Data JPA reports HOUR_OF_DAY: 0 -> 1 exception resolution process and solution

During the data query, the console reported an Caused by: com.mysql.cj.exceptions.WrongArgumentException: HOUR_OF_DAY: 0 -> 1 , and the query found that this was caused by checking the mysql library and converting a field of datetime

There are many solutions on the Internet, most of which are solved by setting the time zone, but unfortunately through testing, I found that even if the data is run on a database with the correct time zone, I still have errors in execution.

Finally I found that my problem was in daylight saving time.

summer time

I remember that when I was a child, there were a few daylight saving times, which roughly meant that I adjusted the watch forward by 1 hour on a certain day, and then adjusted it back by 1 hour on a certain day. This directly causes the problem: xxxx year xx month xx day will correspond to two timestamps.
For example, let's suppose that the day when the watch is adjusted to slow down is at 12 o'clock on October 4, 2021. The specific operation is when the clock passes 12 o'clock on October 4, 2021 for the first time, we adjust the watch to 11 o'clock on October 4, 2021. Therefore, from 11 o'clock to 12 o'clock on October 4, 2021, we will do it again.

Regarding the time, there was a problem with a negative timestamp, which has some meaning: https://segmentfault.com/q/1010000038248983 , if you are interested, you can take a look.

So here comes the problem, than we record the user's birth time, accurate to the minute. If the person entered 11:20 on October 4, 2021, then our system cannot accurately determine whether this time is the first 11:20 or the second 11:20 after 1 hour. point.

Daylight saving time brings us another problem. Some times do not correspond to timestamps.
For another example, if we set the table at 0 o'clock on May 1, 2021, and adjust the table to 1 o'clock, the time between 0 o'clock and 1 o'clock on May 1, 2021 will not appear in history, so if we count the birth time, What the user writes is: at 0:30 on May 1, 2021, the data must be fake data.

Troubleshoot

After the daylight saving time is over, let's talk about the investigation process. In fact, not all data will report this exception when querying, so we must find out that special point. Here is the most stupid way to show it:

    boolean last = false;
    int page = 0;
    Pageable pageable = PageRequest.of(page, 1);
    while (!last) {
      try {
        Page<Resident> residents = this.residentRepository.findAll(specification, pageable);
        page++;
        pageable = PageRequest.of(page, 1);
        last = residents.isLast();
      } catch (Exception e) {
        last = true;
        e.printStackTrace();
        this.logger.info("当前页" + pageable.getPageNumber());
      }
    }

The final console print information: 2021-11-04 13:25:38.562 INFO 4226 --- [nio-8081-exec-7] cysservice.ResidentServiceImpl: current page 1089

Then we go to the data table to find out this record:

select * FROM resident limit 1089, 1

We found that this person’s date of birth was April 15, 1947, at 0:0:00. In fact, the user just entered 1947-4-15 on this date, but when we saved it, we automatically added 0: 0: 0. But it just so happens that the timestamp corresponding to this number is just an invalid number.

The test code is as follows:

  @Test
  void time() {
    Calendar calendar = Calendar.getInstance();
    // 启用严格检查模式
    calendar.setLenient(false);
    calendar.set(1947, 3, 15, 0, 0, 0);
    System.out.println(calendar.getTime());
  }

Abnormal content: java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
It is saying: You said that you were born at 0 o'clock, but this JAVA expert checked it, there was no 0 o'clock on April 15, 1947, and the minimum value of the day was 1 o'clock.

Solve the problem

If the problem is found, the solution is the easiest part.

1. Find the historical data that reported the error and change 0 o'clock to 8 o'clock.
2. Find the historical code and change 0 o’clock to 8 o’clock.

  public static Timestamp getTimeStampFormIdNumber(String idNumber) {
    // 进行出生日期赋值
    int year = Integer.valueOf(idNumber.substring(6, 10));
    int month = Integer.valueOf(idNumber.substring(10, 12));
    int day = Integer.valueOf(idNumber.substring(12, 14));

    Calendar calendar = Calendar.getInstance();
-   calendar.set(year, month - 1, day, 0, 0, 0);
+   calendar.set(year, month - 1, day, 12, 0, 0);
    return new Timestamp(calendar.getTimeInMillis());
  }

As for why it is changed to 12 o'clock, it is because I found that the modification of daylight saving time (faster or slower) avoids 12 o'clock, so the time is 12 o'clock, which can effectively avoid the problem of invalid timestamp. Of course, you can still Change it to 13 o'clock, as long as it is not those hours in the early morning, there is no problem.