15 minuutes Time slot Bucket in Impala

[#1]

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.

[#2 · Accepted solution]

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)

[#3]

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;

[#4 · Accepted solution]

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)

[#5 · Accepted solution]

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

[#6]

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