question Re: 15 minuutes Time slot Bucket in Impala in Archives of Support Questions (Read Only)

15 minuutes Time slot Bucket in Impala

Moazzam — Fri, 16 Sep 2022 09:21:36 GMT

Hi All I have to create a 15 minute time slot in a report,but Iam unable to do so. The table structure is this

CallingCountry CalledCountry TimeStamp Call Duration(Sec)

----------------------------------------------------------------------

India USA 2015-01-01 10:00:00 45

India USA 2015-01-01 10:01:00 30

India USA 2015-01-01 10:05:00 15

India USA 2015-01-01 10:14:00 40

So the Output will be

India- USA 2015-01-01 10:00:00 to 2015-01-01 10:14:59 130

There will be more country like this the date wil be fix it will be in filter. I just want a 15 min time slot for a fixed date chosen by user. I tried many codes but failed,please help me. Any ideas will be appreciated.

Re: 15 minuutes Time slot Bucket in Impala

athtsang — Fri, 13 Feb 2015 08:14:23 GMT

Clumsy but works, actually seems quite efficiently

select now(), 
concat(
cast(seconds_add(trunc(now(), 'hh'), 
            (extract(now(), 'MINUTE') - 
             extract(now(), 'MINUTE') % 15) * 60) as string),
' to ',
cast(seconds_add(trunc(now(), 'hh'), 
            (extract(now(), 'MINUTE') - 
             extract(now(), 'MINUTE') % 15) * 60 + 899) as string)) timeslot;

Re: 15 minuutes Time slot Bucket in Impala

Moazzam — Fri, 13 Feb 2015 10:06:16 GMT

Hi,

thanks for quick reply but my impala version not supporting trunc and extract so I have to do like this

select now(), concat( cast(seconds_add(hour(now()), (minute(now()) -
minute(now()) % 15) * 60) as string), ' to ', cast(seconds_add(hour(now()), (minute(now()) -
minute(now()) % 15) * 60 + 899) as string)) timeslot;

But it is giving an error

AnalysisException: No matching function with those arguments: seconds_add (INT, BIGINT)

Re: 15 minuutes Time slot Bucket in Impala

athtsang — Mon, 16 Feb 2015 01:53:28 GMT

hour(timestamp) returns an INT, but trunc(timestamp, ...) returns a timestamp. So only trunc() can use with seconds_add().

If you have from_unixtime() and unix_timestamp(), you can apply the same concept (this seems even cleaner):

select now(), 
concat(
cast(from_unixtime(
     unix_timestamp(now()) - unix_timestamp(now()) % 900) as string),
' to ',
cast(from_unixtime(899 +
     unix_timestamp(now()) - unix_timestamp(now()) % 900) as string)
) timeslot

Re: 15 minuutes Time slot Bucket in Impala

Moazzam — Mon, 16 Feb 2015 08:42:59 GMT

A million time thanks to you, May God bless you. It worked beautilfully, iam trying understand the code you have written.