Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

15 minuutes Time slot Bucket in Impala

avatar
Explorer

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 SOLUTIONS

avatar
Explorer

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)

 

 

View solution in original post

avatar
Expert Contributor

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

 

View solution in original post

4 REPLIES 4

avatar
Expert Contributor

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;

 

 

 

avatar
Explorer

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)

 

 

avatar
Expert Contributor

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

 

avatar
Explorer

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