[SPARK-30793] Wrong truncations of timestamps before the epoch to minutes and seconds

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.0, 2.3.4, 2.4.5, 3.0.0
• Fix Version/s: 2.4.6, 3.0.0
• Component/s: SQL
• Labels: correctness
• Assignee: Max Gekk
• Reporter: Max Gekk
• Created: 11/Feb/20 18:21
• Updated: 18/Feb/20 04:29
• Resolved: 17/Feb/20 15:15

Description

Truncations to seconds and minutes of timestamps after the epoch are correct:

spark-sql> select date_trunc('SECOND', '2020-02-11 00:01:02.123'), date_trunc('SECOND', '2020-02-11 00:01:02.789');
2020-02-11 00:01:02	2020-02-11 00:01:02

but truncations of timestamps before the epoch are incorrect:

spark-sql> select date_trunc('SECOND', '1960-02-11 00:01:02.123'), date_trunc('SECOND', '1960-02-11 00:01:02.789');
1960-02-11 00:01:03	1960-02-11 00:01:03

The result must be *1960-02-11 00:01:02 1960-02-11 00:01:02*

The same for the MINUTE level:

spark-sql> select date_trunc('MINUTE', '1960-02-11 00:01:01'), date_trunc('MINUTE', '1960-02-11 00:01:50');
1960-02-11 00:02:00	1960-02-11 00:02:00

The result must be 1960-02-11 00:01:00 1960-02-11 00:01:00

Issue Links

• is caused by: SPARK-22829 Add new built-in function date_trunc()
• links to: GitHub Pull Request #27543
• links to: GitHub Pull Request #27611