[SPARK-30766] Wrong truncation of old timestamps to hours and days

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: Max Gekk
• Reporter: Max Gekk
• Created: 09/Feb/20 17:28
• Updated: 14/Feb/20 14:35
• Resolved: 14/Feb/20 14:35

Description

The date_trunc() function incorrectly truncates old timestamps to HOUR and DAY:

scala> import  org.apache.spark.sql.functions._
import org.apache.spark.sql.functions._

scala> :paste
// Entering paste mode (ctrl-D to finish)

Seq("0010-01-01 01:02:03.123456").toDF()
    .select($"value".cast("timestamp").as("ts"))
    .select(date_trunc("HOUR", $"ts").cast("string"))
    .show(false)

// Exiting paste mode, now interpreting.

+------------------------------------+
|CAST(date_trunc(HOUR, ts) AS STRING)|
+------------------------------------+
|0010-01-01 01:30:17                 |
+------------------------------------+

the result must be 0010-01-01 01:00:00

scala> :paste
// Entering paste mode (ctrl-D to finish)

Seq("0010-01-01 01:02:03.123456").toDF()
    .select($"value".cast("timestamp").as("ts"))
    .select(date_trunc("DAY", $"ts").cast("string"))
    .show(false)

// Exiting paste mode, now interpreting.

+-----------------------------------+
|CAST(date_trunc(DAY, ts) AS STRING)|
+-----------------------------------+
|0010-01-01 23:30:17                |
+-----------------------------------+

the result must be 0010-01-01 00:00:00

Issue Links

• links to: GitHub Pull Request #27512