[SPARK-36723] day-time interval types should respect daylight saving time correctly

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Kousuke Saruta
• Created: 11/Sep/21 13:10
• Updated: 11/Sep/21 13:11

Description

In the current master, day-time interval types handle 24 hours as 1 day regardless of the time zone.
So, some operation with day-time interval data doesn't respect daylight saving time.

spark-sql> SET spark.sql.legacy.interval.enabled=false;
spark-sql> SET spark.sql.session.timeZone America/Los_Angeles

spark-sql> SELECT timestamp'2019-03-10 01:00:00' + INTERVAL '1' DAY;
2019-03-11 01:00:00 -- OK. Expected result.

spark-sql> SELECT timestamp'2019-03-10 01:00:00' + INTERVAL '24' HOUR;
2019-03-11 01:00:00 -- Not OK. 2019-03-11 02:00:00 is expected.

On the other hand, non-ANSI interval types properly handle daylight saving time.

spark-sql> SET spark.sql.legacy.interval.enabled=true;
spark-sql> SET spark.sql.session.timeZone America/Los_Angeles

spark-sql> SELECT timestamp'2019-03-10 01:00:00' + INTERVAL '1' DAY;
2019-03-11 01:00:00

spark-sql> SELECT timestamp'2019-03-10 01:00:00' + INTERVAL '24' HOUR;
2019-03-11 02:00:00