[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&focusedCommentId=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&focusedCommentId=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)