[ 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','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.2#803003)