Spring data JPA report hour_ OF_ Day: 0 – > 1 exception resolution process and solution

Time:2021-12-30

During data query, the console reported an errorCaused by: com.mysql.cj.exceptions.WrongArgumentException: HOUR_OF_DAY: 0 -> 1Exception, the query shows that this is due to the query of MySQL database, and the conversion type isdatetimeCaused by a field of type.

There are many online solutions, most of which are solved by setting the time zone. Unfortunately, through the test, I found that even if I ran the data on the database with the correct time zone, I still made mistakes in execution.

Finally, I found that my problem appeared in daylight saving time.

Daylight saving time

I remember when I was a child, I had several daylight saving hours. The general meaning is to set the watch up one hour on a certain day, and then set the watch down another hour on a certain day. The problem directly caused by this is that the XX day of XXXX will correspond to the last two timestamps.
For example, let’s assume that the day when we slow down the watch is 12:00 on October 4, 2021. The specific operation is that the clock passed 12:00 on October 4, 2021 for the first time, and we adjusted the watch to 11:00 on October 4, 2021. So we will do it again from 11:00 to 12:00 on October 4, 2021.

As for time, there is a problem that the timestamp is negative, which is also interesting:https://segmentfault.com/q/1010000038248983, those who are interested can have a look.

So here’s the problem. It’s more accurate than we record the user’s birth time to minutes. If this person enters 11:20 on October 4, 2021, our system cannot accurately judge whether this time is the first 11:20 or the second 11:20 after one hour.

Daylight saving time also brings us another problem. Some times do not correspond to time stamps.
For another example, if we set it to 0:00 on May 1, 2021 and turn the table forward by 1:00, the time from 0:00 to 1:00 on May 1, 2021 will not appear in history. Therefore, if we count the birth time point, the user writes: 0:30 on May 1, 2021, the data must be false.

Check

After daylight saving time, let’s talk about the troubleshooting process. In fact, not all data will report such exceptions during query, so find out the special point. Here is the most stupid display method:

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 ("current page" + pageable. Getpagenumber());
      }
    }

Final console print information:2021-11-04 13:25:38.562 INFO 4226 --- [nio-8081-exec-7] c.y.s.service. Residentserviceimpl: current page 1089

Then we go to the data sheet and find out this record:

select * FROM resident limit 1089, 1

Spring data JPA report hour_ OF_ Day: 0 - > 1 exception resolution process and solution

We found that the person’s date of birth was 0:00 on April 15, 1947. In fact, the user only entered the date 1947-4-15, but we automatically added 0 hour, 0 minute and 0 second when saving. But it happens that this number corresponds to the timestamp, which is exactly an invalid number.

The test code is as follows:

@Test
  void time() {
    Calendar calendar = Calendar.getInstance();
    //Enable strict check mode
    calendar.setLenient(false);
    calendar.set(1947, 3, 15, 0, 0, 0);
    System.out.println(calendar.getTime());
  }

Exception content:java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
It means: you said you were born at 0 o’clock, but this Java bull checked that there was no 0 o’clock on April 15, 1947, and the minimum value of that day was 1 o’clock.

solve the problem

When the problem is found, the solution is the simplest link.

  1. Find the historical data of error reporting and change 0 point to 8 point.
  2. Find the historical code and change 0 point to 8 point.
public static Timestamp getTimeStampFormIdNumber(String idNumber) {
    //Assign birth date
    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, I found that the modification of daylight saving time (speed up or slow down) avoids 12 o’clock, so the time is 12 o’clock, which can effectively avoid the problem of invalid timestamp. Of course, you can also change it to 13 o’clock, as long as it is not the hours in the early morning, there is no problem.

Recommended Today

IOS bottom layer exploration: block (V) — block source code analysis (\u what does the bottom layer do?)

review In the previous blog, through theblockTrace the source,Assembly trace mode, source code analysis, on the underlying structure andblockHave a certain understanding of the attributes and methods of, so this blog will continue toblockThe bottom layer of. Block exploration analysis Block of IOS bottom layer exploration (I) — getting to know block (how many blocks […]