[SPARK-31150] Parsing seconds fraction with variable length for timestamp

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: Kent Yao 2
• Reporter: Kent Yao 2
• Created: 13/Mar/20 18:00
• Updated: 10/Apr/20 07:44
• Resolved: 17/Mar/20 14:11

Description

This JIRA is to support parsing timestamp values with variable length second fraction parts.

e.g. 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]' can parse timestamp with 0~6 digit-length second fraction but fail >=7

select to_timestamp(v, 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]') from values
 ('2019-10-06 10:11:12.'),
 ('2019-10-06 10:11:12.0'),
 ('2019-10-06 10:11:12.1'),
 ('2019-10-06 10:11:12.12'),
 ('2019-10-06 10:11:12.123UTC'),
 ('2019-10-06 10:11:12.1234'),
 ('2019-10-06 10:11:12.12345CST'),
 ('2019-10-06 10:11:12.123456PST') t(v)
2019-10-06 03:11:12.123
2019-10-06 08:11:12.12345
2019-10-06 10:11:12
2019-10-06 10:11:12
2019-10-06 10:11:12.1
2019-10-06 10:11:12.12
2019-10-06 10:11:12.1234
2019-10-06 10:11:12.123456

select to_timestamp('2019-10-06 10:11:12.1234567PST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
NULL

Since 3.0, we use java 8 time API to parse and format timestamp values. when we create the DateTimeFormatter, we use appendPattern to create the build first, where the 'S..S' part will be parsed to a fixed-length(= 'S..S'.length). This fits the formatting part but too strict for the parsing part because the trailing zeros are very likely to be truncated.

Issue Links

• links to: GitHub Pull Request #27906
• links to: GitHub Pull Request #27949