[jira] [Comment Edited] (DRILL-7371) DST/UTC cast/to_timestamp problem

["benj (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-7371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16927527#comment-16927527 ] 

benj edited comment on DRILL-7371 at 3/12/20, 4:19 PM:
-------------------------------------------------------

[~volodymyr], the problem occurs on all Daylight Saving Time of Europe (Paris).

From investigation after your message, it's appears that the problem appears in console mode (/bin/sqlline -u jdbc:drill:zk=...:2181;schema=myhdfs).
 The result is almost wrong with an execution via zeppelin (jdbc too).
 But there is no problem with the request launched directly in the Apache Drill web interface ([http://...:8047/query]) and no problem with request in drill-embedded

So it's seems that the problem probably comes with JDBC.

 


was (Author: benj641):
[~volodymyr], the problem occurs on all Daylight Saving Time of Europe (Paris).

From investigation after your message, it's appears that the problem appears in console mode (/bin/sqlline -u jdbc:drill:zk=...:2181;schema=myhdfs).
 The result is almost wrong with an execution via zeppelin (jdbc too).
 But there is no problem with the request launched directly in the Apache Drill web interface ([http://...:8047/query).]

So it's seems that the problem probably comes with JDBC.

 

> DST/UTC cast/to_timestamp problem
> ---------------------------------
>
>                 Key: DRILL-7371
>                 URL: https://issues.apache.org/jira/browse/DRILL-7371
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Client - JDBC, Functions - Drill
>    Affects Versions: 1.16.0
>            Reporter: benj
>            Priority: Major
>
> 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])
> {code:sql}
> SELECT TIMEOFDAY();
> +-----------------------------+
> |           EXPR$0            |
> +-----------------------------+
> | 2019-09-11 08:20:12.247 UTC |
> +-----------------------------+
> {code}
> 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:
> {code:sql}
> 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 |
> +-----------------------+
> {code}
> 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:
> {code:java}
> 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 |
> +-----------------------+
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)