[IMPALA-8108] Impala query returns TIMESTAMP values in different types

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Backend
• Epic Color: ghx-label-3
• Assignee: Unassigned
• Reporter: Robbie Zhang
• Created: 24/Jan/19 12:30
• Updated: 09/Sep/21 00:09

Description

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:

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;

The output is:

+-------------------------------+
|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|
+-------------------------------+

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.