[ https://issues.apache.org/jira/browse/IMPALA-8108?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16803682#comment-16803682 ]
Balazs Jeszenszky commented on IMPALA-8108: ------------------------------------------- Not sure if this is a good idea - if someone requires a certain format, it's best to use a specific format string, and I wouldn't expect every timestamp to have a bunch of trailing zeroes. [~grahn] thoughts? > Impala query returns TIMESTAMP values in different types > -------------------------------------------------------- > > Key: IMPALA-8108 > URL: https://issues.apache.org/jira/browse/IMPALA-8108 > Project: IMPALA > Issue Type: Improvement > Components: Backend > Reporter: Robbie Zhang > Assignee: Robbie Zhang > Priority: Major > > When a timestamp has a .000 or .000000 or .000000000 (when fraction value is > zeros) the timestamp is displayed with no fraction of second. For example: > {code:java} > select cast(ts as timestamp) from > (values > ('2019-01-11 10:40:18' as ts), > ('2019-01-11 10:40:19.0'), > ('2019-01-11 10:40:19.00'), > ('2019-01-11 10:40:19.000'), > ('2019-01-11 10:40:19.0000'), > ('2019-01-11 10:40:19.00000'), > ('2019-01-11 10:40:19.000000'), > ('2019-01-11 10:40:19.0000000'), > ('2019-01-11 10:40:19.00000000'), > ('2019-01-11 10:40:19.000000000'), > ('2019-01-11 10:40:19.000000001') > ) t;{code} > The output is: > {code:java} > +-------------------------------+ > |cast(ts as timestamp)| > +-------------------------------+ > |2019-01-11 10:40:18| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19| > |2019-01-11 10:40:19.000000001| > +-------------------------------+ > {code} > As we can see, values of the same column are returned in two different types. > The inconsistency breaks some downstream use cases. > The reason is that impala uses function > boost::posix_time::to_simple_string(time_duration) to convert timestamp to a > string and to_simple_string() remove fractional seconds if they are all > zeros. Perhaps we can append ".000000000" if the length of the string is 8 > (HH:MM:SS). > For now we can work around it by using function from_timestamp(ts, > 'yyyy-mm-dd hh:mm.ss.sssssssss') to unify the output (convert to string), or > using function millisecond(ts) to get fractional seconds. -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-all-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org