[
https://issues.apache.org/jira/browse/IMPALA-8108?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Robbie Zhang reassigned IMPALA-8108:
------------------------------------
Assignee: Robbie Zhang
> 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]
