[Feature Request] Handling optional parts in date format

[nicovak]

Hello,

I think It could be a nice feature to handle optional parts in date format with []
For example in my case, I can have DateTime with second fractions or not (it's adaptive).

Actually, I need to perform a Substr :

FromDateTime(Substr(JSONPATH(payload, '$.created_at'), 0, 18), 'yyyy-MM-dd''T''HH:mm:ss')

But It could be handled like that :

FromDateTime(JSONPATH(payload, '$.created_at'), 'yyyy-MM-dd''T''HH:mm:ss[.SSS]''Z''')

It's already provided by Java 8 and DateTimeFormatter, in documentation we have :

   [       optional section start
   ]       optional section end

[Jackie-Jiang]

We don't support it right now because we are using joda library and haven't moved to java library for performance reason. Seems the author of joda is also recommending switching to java library, and this is another motivation for us to switch. @richardstartin how much performance difference do you observe between joda and java date time format?

[KKcorps]

From my benchmarks, java time seems 20-30% slower than Joda time.

Benchmark                                  Mode  Cnt     Score     Error  Units
BenchmarkJoda.benchmarkJavaTime            avgt    5  6077.279 ± 282.175  us/op
BenchmarkJoda.benchmarkJavaTimePerCompute  avgt    5  6055.735 ± 254.637  us/op
BenchmarkJoda.benchmarkJoda                avgt    5  4671.964 ±  93.222  us/op

Code - https://gist.github.com/KKcorps/19b6b093bfa15b7d62b27db0b8012e51

[Jackie-Jiang]

@KKcorps Thanks for the benchmark! Can we also add a benchmark for parsing the formatted timestamp to long?

[KKcorps]

Benchmark                                     Mode  Cnt     Score      Error  Units
BenchmarkJoda.benchmarkJavaDateStringToEpoch  avgt    5  9241.257 ± 1233.009  us/op
BenchmarkJoda.benchmarkJodaStringToEpoch      avgt    5  7330.508 ±  399.998  us/op

Updated the gist with code for this as well. Joda is faster in both cases. We don't have much choice though since joda is officially in maintenance phase.

[KKcorps]

The above benchmarks were for Java 11. For Java 17, they seem much closer. And converting String to Epoch is actually faster with java.time

Benchmark                                     Mode  Cnt     Score      Error  Units
BenchmarkJoda.benchmarkJavaTime               avgt    5  4986.148 ±  551.520  us/op
BenchmarkJoda.benchmarkJavaTimePreCompute     avgt    5  4075.269 ±  691.534  us/op
BenchmarkJoda.benchmarkJoda                   avgt    5  3719.916 ±  319.401  us/op


BenchmarkJoda.benchmarkJavaDateStringToEpoch  avgt    5  6362.567 ± 1251.276  us/op
BenchmarkJoda.benchmarkJodaStringToEpoch      avgt    5  9887.073 ± 1456.975  us/op

[Jackie-Jiang]

Cool! Since the difference is not big, I think we should start migrating to java library

[richardstartin]

The decision requires a little more due diligence than that ;) I have seen much larger differences in favour of Joda recently, and the bit directly above those results which says "REMEMBER: The numbers below are just data. To gain reusable insights, you need to follow up on why the numbers are the way they are." has been omitted.

[richardstartin]

Case in point, I found that Joda was 6x faster than Java time for year conversion here: #8397 (comment)

There has been some progress in java.time recently:

• 8283681: Improve ZonedDateTime offset handling openjdk/jdk#7957
• 8283774: TestZoneOffset::test_immutable should ignore ZoneOffset::rules openjdk/jdk#7989
• 8283781: Avoid allocating unused lastRulesCaches openjdk/jdk#7990
• 8283782: Redundant verification of year in LocalDate::ofEpochDay openjdk/jdk#8014
• 8283996: Reduce cost of year and month calculations openjdk/jdk#8039

Some of these improvements will be backported to JDK17, so we should reassess once Pinot can run on JDK17.