[SPARK-36065] date_trunc returns incorrect output

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.0, 3.3.0
• Component/s: SQL
• Labels: date_trunc sql timestamp
• Assignee: Unassigned
• Reporter: Sumeet
• Created: 09/Jul/21 03:33
• Updated: 30/Jul/21 08:39

Description

Hi,

Running date_trunc on any hour of "1891-10-01" returns incorrect output for "Europe/Bratislava" timezone.
Use the following steps in order to reproduce the issue:

• Run spark-shell using:

TZ="Europe/Bratislava" ./bin/spark-shell --conf spark.driver.extraJavaOptions='-Duser.timezone=Europe/Bratislava' --conf spark.executor.extraJavaOptions='-Duser.timezone=Europe/Bratislava' --conf spark.sql.session.timeZone="Europe/Bratislava"

• Generate test data:

((0 until 9).map(hour => s"1891-10-01 00:0$hour:00") ++ (10 until 24).map(hour => s"1891-10-01 00:$hour:00")).toDF("ts_string").createOrReplaceTempView("temp_ts")

• Run query:

sql("select ts_string, cast(ts_string as TIMESTAMP) as ts, date_trunc('day', ts_string) from temp_ts").show(false)

• Output:

+-------------------+-------------------+--------------------------+
|ts_string          |ts                 |date_trunc(day, ts_string)|
+-------------------+-------------------+--------------------------+
|1891-10-01 00:00:00|1891-10-01 00:02:16|1891-10-01 00:02:16       |
|1891-10-01 00:01:00|1891-10-01 00:03:16|1891-10-01 00:02:16       |
|1891-10-01 00:02:00|1891-10-01 00:04:16|1891-10-01 00:02:16       |
|1891-10-01 00:03:00|1891-10-01 00:03:00|1891-10-01 00:02:16       |
|1891-10-01 00:04:00|1891-10-01 00:04:00|1891-10-01 00:02:16       |
|1891-10-01 00:05:00|1891-10-01 00:05:00|1891-10-01 00:02:16       |
|1891-10-01 00:06:00|1891-10-01 00:06:00|1891-10-01 00:02:16       |
|1891-10-01 00:07:00|1891-10-01 00:07:00|1891-10-01 00:02:16       |
|1891-10-01 00:08:00|1891-10-01 00:08:00|1891-10-01 00:02:16       |
|1891-10-01 00:10:00|1891-10-01 00:10:00|1891-10-01 00:02:16       |
|1891-10-01 00:11:00|1891-10-01 00:11:00|1891-10-01 00:02:16       |
|1891-10-01 00:12:00|1891-10-01 00:12:00|1891-10-01 00:02:16       |
|1891-10-01 00:13:00|1891-10-01 00:13:00|1891-10-01 00:02:16       |
|1891-10-01 00:14:00|1891-10-01 00:14:00|1891-10-01 00:02:16       |
|1891-10-01 00:15:00|1891-10-01 00:15:00|1891-10-01 00:02:16       |
|1891-10-01 00:16:00|1891-10-01 00:16:00|1891-10-01 00:02:16       |
|1891-10-01 00:17:00|1891-10-01 00:17:00|1891-10-01 00:02:16       |
|1891-10-01 00:18:00|1891-10-01 00:18:00|1891-10-01 00:02:16       |
|1891-10-01 00:19:00|1891-10-01 00:19:00|1891-10-01 00:02:16       |
|1891-10-01 00:20:00|1891-10-01 00:20:00|1891-10-01 00:02:16       |
+-------------------+-------------------+--------------------------+
only showing top 20 rows