Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Week Aggregation in Hive

Highlighted

Week Aggregation in Hive

New Contributor
 
4 REPLIES 4

Re: Week Aggregation in Hive

Super Guru

@Gayathri Devi

As you are having timestamp column so pass that column in the below query.
Hive in built have weekofyear function to get week number

We can create start day and end of day for the given timestamp column.

Try with below query

hive>  select concat("Week ",weekofyear(current_timestamp)) as weeknumber,date_format(date_sub(current_Timestamp,pmod(datediff(current_timestamp,'1900-01-07'),7)),"MMMMM dd,yyyy") as startday,date_format(date_add(current_timestamp,6 - pmod(datediff(current_timestamp,"1900-01-07"),7)),"MMMMM dd,yyyy") as endday;
+-------------+--------------------+--------------------+--+
| weeknumber  |      startday      |       endday       |
+-------------+--------------------+--------------------+--+
| Week 39     | September 23,2018  | September 29,2018  |
+-------------+--------------------+--------------------+--+

(or)

if you don't need formatting then use below query.

hive> select weekofyear(current_timestamp) as weeknumber,date_sub(current_Timestamp,pmod(datediff(current_timestamp,'1900-01-07'),7))as startday,date_add(current_timestamp,6 - pmod(datediff(current_timestamp,"1900-01-07"),7))as endday;
+-------------+-------------+-------------+--+
| weeknumber  |  startday   |   endday    |
+-------------+-------------+-------------+--+
| 39          | 2018-09-23  | 2018-09-29  |
+-------------+-------------+-------------+--+

Just replace current_timestamp with your timestamp column.

Re: Week Aggregation in Hive

Super Guru
@Gayathri Devi

Yes, It's possible.

Try with below query:

hive> select concat("Week ",weekofyear(current_timestamp)) as weeknumber,date_format(date_add(date_sub(current_timestamp,pmod(datediff(current_timestamp,'1900-01-07'),7)),1),"MMMMM dd,yyyy")startday,date_format(date_add(current_timestamp,7 - pmod(datediff(current_timestamp,"1900-01-07"),7)),"MMMMM dd,yyyy") endday;
+-------------+--------------------+--------------------+--+
| weeknumber  |      startday      |       endday       |
+-------------+--------------------+--------------------+--+
| Week 39     | September 24,2018  | September 30,2018  |
+-------------+--------------------+--------------------+--+

if you want full name of day also then

hive> select concat("Week ",weekofyear(current_timestamp)) as weeknumber,date_format(date_add(date_sub(current_timestamp,pmod(datediff(current_timestamp,'1900-01-07'),7)),1),"MMMMM EEEEE dd,yyyy")startday,date_format(date_add(current_timestamp,7 - pmod(datediff(current_timestamp,"1900-01-07"),7)),"MMMMM EEEEE dd,yyyy") endday;
+-------------+---------------------------+---------------------------+--+
| weeknumber  |         startday          |          endday           |
+-------------+---------------------------+---------------------------+--+
| Week 39     | September Monday 24,2018  | September Sunday 30,2018  |
+-------------+---------------------------+---------------------------+--+

Re: Week Aggregation in Hive

New Contributor

select weekofyear('2018-12-31');

This returns 1 instead of 52; Can you please justify?

,

select weekofyear('2018-12-31'); returns 1; Can you please justify.

Re: Week Aggregation in Hive

Super Guru

@ujvala reddy

Reason is

The first Week of Year is the first week with 4 or more days in the new year.
First day of week is Monday and last day of week is Sunday

Refer to this thread for more details regards to this week of year.

Don't have an account?
Coming from Hortonworks? Activate your account here