[DRILL-7139] Date_add() can produce incorrect results when adding to a timestamp

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.15.0
• Fix Version/s: 1.17.0
• Component/s: Functions - Drill
• Assignee: Dmytriy Grinchenko
• Reporter: Robert Hou
• Reviewer: Igor Guzenko
• Created: 29/Mar/19 01:23
• Updated: 13/Jun/19 11:24
• Resolved: 13/Jun/19 11:24

Description

I am using date_add() to create a sequence of timestamps:

select date_add(timestamp '1970-01-01 00:00:00', cast(concat('PT',107374,'M') as interval minute)) timestamp_id from (values(1));
+--------------------------+
|       timestamp_id       |
+--------------------------+
| 1970-01-25 20:31:12.704  |
+--------------------------+
1 row selected (0.121 seconds)

When I add one more, I get an older timestamp:

select date_add(timestamp '1970-01-01 00:00:00', cast(concat('PT',107375,'M') as interval minute)) timestamp_id from (values(1));
+--------------------------+
|       timestamp_id       |
+--------------------------+
| 1969-12-07 03:29:25.408  |
+--------------------------+
1 row selected (0.126 seconds)

Issue Links

• is related to: DRILL-4782 TO_TIME function cannot separate time from date time string