[SPARK-27224] Spark to_json parses UTC timestamp incorrectly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 2.3.0
• Component/s: Spark Core
• Assignee: Unassigned
• Reporter: Jeff Xu
• Created: 20/Mar/19 22:59
• Updated: 12/Dec/22 18:10
• Resolved: 21/Mar/19 00:57

Description

When parsing ISO-8601 timestamp, if there is UTC suffix symbol, and more than 3 digits in the fraction part, from_json will give incorrect result.

 

scala> val schema = new StructType().add("t", TimestampType)

#
# no "Z", no problem
#

scala> val t = "2019-03-20T09:01:03.1234567"
scala> Seq((s"""{"t":"${t}"}""")).toDF("json").select(from_json(col("json"), schema)).show(false)
+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 09:01:03.123]|
+-------------------------+

#
# Add "Z", incorrect
#

scala> val t = "2019-03-20T09:01:03.1234567Z"
scala> Seq((s"""{"t":"${t}"}""")).toDF("json").select(from_json(col("json"), schema)).show(false)
+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:21:37.567]|
+-------------------------+

#
# reduce the # of digits, the conversion is incorrect until only we reach 3 digits
#

scala> val t = "2019-03-20T09:01:03.123456Z"

+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:03:06.456]|
+-------------------------+

scala> val t = "2019-03-20T09:01:03.12345Z

+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:01:15.345]|
+-------------------------+


scala> val t = "2019-03-20T09:01:03.1234Z"

+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:01:04.234]|
+-------------------------+


# correct when there is <=3 digits in fraction


scala> val t = "2019-03-20T09:01:03.123Z"

+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:01:03.123]|
+-------------------------+


scala> val t = "2019-03-20T09:01:03.999Z"

+-------------------------+
|jsontostructs(json)      |
+-------------------------+
|[2019-03-20 02:01:03.999]|
+-------------------------+

 

This could be related to SPARK-17914.

Issue Links

• relates to: SPARK-29904 Parse timestamps in microsecond precision by JSON/CSV datasources