[SPARK-30857] Wrong truncations of timestamps before the epoch to hours and days

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.0, 2.3.4, 2.4.5
• Fix Version/s: 2.4.6
• Component/s: SQL
• Labels: correctness
• Assignee: Max Gekk
• Reporter: Max Gekk
• Created: 17/Feb/20 17:24
• Updated: 18/Feb/20 20:19
• Resolved: 18/Feb/20 13:31

Description

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

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

but truncations of timestamps before the epoch are incorrect:

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

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

The same for the DAY level:

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

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

Issue Links

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