[DRILL-7371] DST/UTC cast/to_timestamp problem

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.16.0
• Component/s: Client - JDBC, Functions - Drill
• Assignee: Unassigned
• Reporter: benj
• Created: 11/Sep/19 08:44
• Updated: 12/Mar/20 16:19

Description

With LC_TIME=fr_FR.UTF-8 and drillbits configured in UTC (like specified in http://www.openkb.info/2015/05/understanding-drills-timestamp-and.html#.VUzhotpVhHw find from https://drill.apache.org/docs/data-type-conversion/#to_timestamp)

SELECT TIMEOFDAY();
+-----------------------------+
|           EXPR$0            |
+-----------------------------+
| 2019-09-11 08:20:12.247 UTC |
+-----------------------------+

Problems appears when cast/to_timestamp date (date related to the DST (Daylight Save Time) of some countries).

To illustrate, all the next requests give the same wrong results:

SELECT to_timestamp('2018-03-25 02:22:40 UTC','yyyy-MM-dd HH:mm:ss z');
SELECT to_timestamp('2018-03-25 02:22:40','yyyy-MM-dd HH:mm:ss');
SELECT cast('2018-03-25 02:22:40' as timestamp);
SELECT cast('2018-03-25 02:22:40 +0000' as timestamp);
+-----------------------+
|        EXPR$0         |
+-----------------------+
| 2018-03-25 03:22:40.0 |
+-----------------------+

while the result should be "2018-03-25 02:22:40.0"

An UTC date and time in string shouldn't change when casting to UTC timestamp.
To illustrate, the next requests produce good results:

SELECT to_timestamp('2018-03-26 02:22:40 UTC','yyyy-MM-dd HH:mm:ss z');
+-----------------------+
|        EXPR$0         |
+-----------------------+
| 2018-03-26 02:22:40.0 |
+-----------------------+

SELECT CAST('2018-03-24 02:22:40' AS timestamp);
+-----------------------+
|        EXPR$0         |
+-----------------------+
| 2018-03-24 02:22:40.0 |
+-----------------------+