CODEWITHSUNDEEP
hive
Huzefa
Huzefa

Reputation: 119

Hive: Filtering rows based on a range of Time

I am new to this forum and this is my first question. i did search for solution on this forum but still unable to get the exact solution. If such a type of question is already answered then my apologies in advance.

Now coming back to my problem. I have a HIVE table "table1" having following columns: 

channel_name     string
start_time       string
prog_name        string
cost             double

Below is the sample date from the above table: 

BBC      2016-11-24 05:47:02     NEW: Wonder World       191.0
GTV      2016-11-24 21:35:58     NEW: Great Escape       99.0
BBC      2016-11-25 21:43:29     NEW: Wonder World       131.0
GTV      2016-11-25 23:32:56     NEW: STATE OF FEAR      145.0
GTV      2016-11-26 01:30:30     NEW: Great Escape       128.0

I have to find out cost based on filters on UI where user can filter by program name and a range of time from buckets of 0-5, 6-10, 11-15...19-24 etc. I developed below HIVE query where each row should be selected based on the program and the buckets of time filtered by the user. I have to compare on the time part of the entire timestamp value irrespective of the date.

Below is the HIVE query I tried but it is unable to compare and giving error: 

SELECT sum(cost)
  FROM table1
  WHERE prog_name='NEW: Wonder World'
  AND (cast(substr(start_time, 12) AS TIMESTAMP) BETWEEN (00:00:00
                                                          AND 05:59:59)
       OR cast(substr(start_time, 12) AS TIMESTAMP) BETWEEN (06:00:00
                                                             AND 09:59:59));

FAILED: ParseException line 1:118 cannot recognize input near ':' '00' ':' in expression specification

What is the correct syntax to convert and compare only the time part from the timestamp value? Appreciate any suggestions/pointers for the above query.

Upvotes: 1

Views: 997

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

select  sum(cost)   as sum_cost

from    table1 

where   prog_name='NEW: Wonder World' 
    and substr(start_time,12) between '00:00:00' and '09:59:59'
;

+-----------+
| sum_cost  |
+-----------+
| 191       |
+-----------+

Upvotes: 0

Related Questions