“This is the 32nd day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

Original is not easy, hope more attention, more praise 🙇👍

The accident description

The company’s app client reports some user data to the Java backend service, which has a click time field. Today, when I was patrolling the log, I found a large number of error logs that should save this field.

As follows:

Data truncation: Incorrect datetime value: ‘53884-04-07 04:09:44’ for column ‘clickTime’ at row 1

Pseudo code

/** * String */ according to the DateTime format
public static String dateTimeMillisToString(long time, String pattern) {
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(time);
    return (new SimpleDateFormat(pattern)).format(calendar.getTime());
}

/** * Saves the user data reported by the client */
public void save(User user) {
    // Note that the time *1000 is converted to milliseconds
    String time = dateTimeMillisToString(user.getClickTime() * 1000."yyyy-MM-dd HH:mm:ss");
    user.setCreateTime(time);
    save(user);
}
Copy the code

guess

Based on the exception log and source code, we suspect that some clients are reporting in milliseconds instead of seconds.

To verify the conjecture, I decided to write a main method.

The reduction of

public static void main(String[] args) {
    long time1 = 1638263956L;
    long time2 = 1638263956000L;
    System.out.println(dateTimeMillisToString(time1 * 1000."yyyy-MM-dd HH:mm:ss"));
    System.out.println(dateTimeMillisToString(time2 * 1000."yyyy-MM-dd HH:mm:ss"));
}
Copy the code

As expected, it was a millisecond problem.

To solve the problem

String time = user.getClickTime();
if (StringUtils.isNotBlank(time)) {
    if (time.length() == 10) {
        // 10 bits, indicating that the time is in seconds
        time = dateTimeMillisToString(time * 1000, YYYYMMDD_HHMMSS);
    } else if (time.length() == 13) {
        // 13 bits, which indicates that the time is in millisecondstime = dateTimeMillisToString(time, YYYYMMDD_HHMMSS); }}Copy the code

You think this is the end of it?

After the repair, there were more anomalies, the magnitude of which was more than ten times the original. I panicked and hurried to find the operation and maintenance leaders to roll back the version.

The exception log is as follows:

Data truncation: Incorrect datetime value: ‘0’ for column ‘clickTime’ at row 1

Data truncation: Incorrect datetime value: ‘1’ for column ‘clickTime’ at row 1

The client also reported a large number of zeros and ones.

If the length of the field is not 10 or 13, the program does not do any processing and directly inserts the field into the database. In the database table structure, the field is of datetime type. Therefore, an error will be reported when saving 0 or 1.

So what exactly did we save when we converted 0 or 1? Using the main method again:

public static void main(String[] args) {
    System.out.println(dateTimeMillisToString(0 * 1000."yyyy-MM-dd HH:mm:ss"));
    System.out.println(dateTimeMillisToString(1 * 1000."yyyy-MM-dd HH:mm:ss"));
    // A 9-bit timestamp
    System.out.println(dateTimeMillisToString(163826395 * 1000."yyyy-MM-dd HH:mm:ss"));
}
Copy the code

SimpleDateFormat’s format() method will format all numeric types.

This time it really worked out

The code continues to be optimized for compatibility:

String time = user.getClickTime();
if (StringUtils.isNotBlank(time)) {
    // If the length is greater than 10 bits, the time is no longer multiplied by 1000
    if (time.length() > 10) {
        time = dateTimeMillisToString(time, YYYYMMDD_HHMMSS);
    } else {
        time = dateTimeMillisToString(time * 1000, YYYYMMDD_HHMMSS); }}Copy the code

At the same time, communicate with colleagues on the client side to unify the time unit.