Robbie Zhang created IMPALA-8108:
------------------------------------
Summary: 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
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]
