[
https://issues.apache.org/jira/browse/IMPALA-8108?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16805206#comment-16805206
]
Greg Rahn commented on IMPALA-8108:
-----------------------------------
I think there should be a way to specify a constant format for text output. As
mentioned, one way is to do this is to use a cast function in the SQL query the
other (better way) would be to add a formatting option in the client. I
believe the user that requested this is using impala-shell so seems the best
way would be just to add a formatting option there, but given the situation
with Beeswax returning strings, it probably makes that path more difficult.
> 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: [email protected]
For additional commands, e-mail: [email protected]
