SIMPLE_DATE_FORMAT have a different behavior for `X` letter compared to Java

[snleee]

Java's SimpleDateFormat (https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) has a X letter to represent the time zone in the format of -08:00. The behavior of Pinot's SIMPLE_DATE_FORMAT in DateTimeFieldSpec is actually different from Java.

input = 2022-11-16 15:22:37.123+00:00
SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss.SSSX <- this is the config based on Java doc but it's not working
SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss.SSSZ <- this is the working config but it's not supposed to work based on Java definition.

Here is the code to reproduce:

  public static void main(String[] args)
      throws ParseException {
    String input = "2022-11-16 15:22:37.123+00:00";

    // This is working and this is the expected behavior based on the Java documentation.
    String pattern = "yyyy-MM-dd HH:mm:ss.SSSX";
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
    Date date = simpleDateFormat.parse(input);
    System.out.println(date.toInstant().toEpochMilli());

    // This is not working. But, it is supposed to work if we follow the convention for SimpleDateFormat
    try {
        String format = "SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss.SSSX";
      DateTimeFormatSpec dateTimeFormatSpec = new DateTimeFormatSpec(format);
      long millis = dateTimeFormatSpec.getDateTimeFormatter().parseMillis(input);
      System.out.println(millis);
    } catch (Exception e) {
      // not working
      System.out.println("failed");
    }

    // This is working and this is the expected behavior based on the documentation from JAVA.
    try {
      String pattern2 = "yyyy-MM-dd HH:mm:ss.SSSZ";
      simpleDateFormat = new SimpleDateFormat(pattern2);
      date = simpleDateFormat.parse(input);
      System.out.println(date.toInstant().toEpochMilli());
    } catch (Exception e) {
      // not working
      System.out.println("failed");
    }

    // This working but Java's SimpleDateFormat's definition for `Z` doesn't include "-00:00" so it supposed to fail.
    String format2 = "SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss.SSSZ";
    DateTimeFormatSpec dateTimeFormatSpec = new DateTimeFormatSpec(format2);
    long millis2 = dateTimeFormatSpec.getDateTimeFormatter().parseMillis(input);
    System.out.println(millis2);
  }

[snleee]

After some investigation, we actually use JODA library for parsing SIMPLE_DATE_FORMAT https://www.joda.org/joda-time/key_format.html

However, our documentation points the Java's SimpleDateFormat.

1. We need to change the documentation to point JODA. <- done
2. We need to discuss if we want to add the Java's SimpleDateFormat support.
   • Found the issue Consider migrating joda-time to java.time #7499
   • We dropped the SimpleDateFormat for a potential performance concern last year

@Jackie-Jiang