[SPARK-30668] to_timestamp failed to parse 2020-01-27T20:06:11.847-0800 using pattern "yyyy-MM-dd'T'HH:mm:ss.SSSz"

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Target Version/s: 3.0.0
• Assignee: Max Gekk
• Reporter: Xiao Li
• Created: 28/Jan/20 22:11
• Updated: 05/Mar/20 10:20
• Resolved: 05/Mar/20 08:57

Description

SELECT to_timestamp("2020-01-27T20:06:11.847-0800", "yyyy-MM-dd'T'HH:mm:ss.SSSz")

This can return a valid value in Spark 2.4 but return NULL in the latest master

*2.4.5 RC2*

scala> sql("""SELECT to_timestamp("2020-01-27T20:06:11.847-0800", "yyyy-MM-dd'T'HH:mm:ss.SSSz")""").show
+----------------------------------------------------------------------------+
|to_timestamp('2020-01-27T20:06:11.847-0800', 'yyyy-MM-dd\'T\'HH:mm:ss.SSSz')|
+----------------------------------------------------------------------------+
|                                                         2020-01-27 20:06:11|
+----------------------------------------------------------------------------+

*2.2.3 ~ 2.4.4* (2.0.2 ~ 2.1.3 doesn't have `to_timestamp`).

spark-sql> SELECT to_timestamp("2020-01-27T20:06:11.847-0800", "yyyy-MM-dd'T'HH:mm:ss.SSSz");
2020-01-27 20:06:11

Issue Links

• relates to: SPARK-26503 Get rid of spark.sql.legacy.timeParser.enabled
• relates to: SPARK-27438 Increase precision of to_timestamp
• links to: GitHub Pull Request #27441
• links to: GitHub Pull Request #27537