You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Balazs Jeszenszky (JIRA)" <ji...@apache.org> on 2019/03/28 08:17:00 UTC
[jira] [Commented] (IMPALA-8108) Impala query returns TIMESTAMP
values in different types
[ 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-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org