Reply
New Contributor
Posts: 3
Registered: ‎12-12-2013

Impala timestamp diff functionality

What is the best way to do a diff on timestamp fields in Impala version 1.2.3 where the difference in milliseconds is required?  

 

Here is my query-

select a.session_key,a.ani,a.event_timestamp "event_start_timetsamp",b.event_timestamp "event_end_timestamp"
from nuance_ivr_logs a, nuance_ivr_logs b
where
a.session_key='{134E1EF7-3A8E-4FA8-A944-89367DEA8FB7}'
and a.session_key=b.session_key
and a.ani=b.ani
and a.event_type='SWIclst' and a.event_attribute_seq=1
and b.event_type='SWIclnd' and a.event_attribute_seq=1;

 

+----------------------------------------+------------+-------------------------------+-------------------------------+

| session_key                            | ani        | event_start_timetsamp         | event_end_timestamp           |

+----------------------------------------+------------+-------------------------------+-------------------------------+

| {134E1EF7-3A8E-4FA8-A944-89367DEA8FB7} | xxxxxxxx | 2013-12-17 17:59:58.677000000 | 2013-12-17 18:02:51.239000000 |

+----------------------------------------+------------+-------------------------------+-------------------------------+

 

I need to add a column for event_duration, which is the diff of event_end_timestamp and event_start_timestamp.

 

Thanks.

Posts: 1,896
Kudos: 433
Solutions: 303
Registered: ‎07-31-2013

Re: Impala timestamp diff functionality

You could do it via some of the date based built-in functions at http://www.cloudera.com/content/cloudera-content/cloudera-docs/Impala/latest/Installing-and-Using-Im...

I'd imagine something such as:

unix_timestamp(to_date(event_end_timestamp)) - unix_timestamp(to_date(event_start_timetsamp))

Should give you an bigint return of the number of milliseconds.