Week Aggregation in Hive

[#1]

@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.

[#2]

@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  |
+-------------+---------------------------+---------------------------+--+

[#3]

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.

[#4]

@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.