[jira] [Commented] (DRILL-7371) DST/UTC cast/to_timestamp problem
[
https://issues.apache.org/jira/browse/DRILL-7371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16927527#comment-16927527
]
benj commented on DRILL-7371:
-
[~vvysotskyi], 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: 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','-MM-dd HH:mm:ss z');
> SELECT to_timestamp('2018-03-25 02:22:40','-MM-dd HH:mm:ss');
> SELECT cast('2018-03-25 02:22:40' as timestamp);
> SELECT cast('2018-03-25 02:22:40 +' 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','-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.2#803003)
[jira] [Commented] (DRILL-7371) DST/UTC cast/to_timestamp problem
[
https://issues.apache.org/jira/browse/DRILL-7371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16927480#comment-16927480
]
Volodymyr Vysotskyi commented on DRILL-7371:
[~benj641], I tried to reproduce this issue, but Drill returns correct results
for queries you have specified.
The locale was set as you have recommended:
{noformat}
localectl
System Locale: LC_TIME=fr_FR.UTF-8
{noformat}
And Drill was configured in UTC.
Could you please provide more info? Is it possible that this issue is
reproduced for specific date ranges? In this case, please provide a date which
should be set on the system.
> 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: 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','-MM-dd HH:mm:ss z');
> SELECT to_timestamp('2018-03-25 02:22:40','-MM-dd HH:mm:ss');
> SELECT cast('2018-03-25 02:22:40' as timestamp);
> SELECT cast('2018-03-25 02:22:40 +' 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','-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.2#803003)
