[SPARK-28171] Difference of interval type conversion between SparkSQL and PostgreSQL

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Later
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Zhu, Lipeng
• Created: 26/Jun/19 12:44
• Updated: 29/Dec/19 00:54
• Resolved: 14/Sep/19 05:40

Description

When calculate between date and interval.

select timestamp '2019-01-01 00:00:00' + interval '1 2:03:04' day to second, timestamp '2019-01-01 00:00:00' + interval '-1 2:03:04' day to second

• PostgreSQL return 2019-01-02 02:03:04    2018-12-31 02:03:04
• SparkSQL return    2019-01-02 02:03:04    2018-12-30 21:56:56

select timestamp '2019-01-01 00:00:00' + interval '1 -2:03:04' day to second, timestamp '2019-01-01 00:00:00' + interval '-1 -2:03:04' day to second

• PostgreSQL return 2019-01-01 21:56:56    2018-12-30 21:56:56
• SparkSQL return    Interval string does not match day-time format of 'd h:m:s.n': '1 -2:03:04'(line 1, pos 50)