CODEWITHSUNDEEP
datehivecounting
Dick McManus
Dick McManus

Reputation: 789

alter weekofyear to sun - sat?

I'm trying to count visitors in my data by week using Hive but my week is to start Sunday, not Monday. Something like this would work but it gives Mon-Sun

SELECT 
Month(timestamp), Weekofyear(timestamp), COUNT (DISTINCT userid) 
FROM Data 
Group By Month, Week

Is there a way to perhaps alter the timestamp where this would work? This didn't quite seem to align with my numbers doing it with hard coded dates.

SELECT 
Month(timestamp), Weekofyear(SUB_DAY(timestamp,1)), COUNT (DISTINCT userid) 
FROM Data 
Group By Month, Week

Upvotes: 1

Views: 112

Answers (2)

Dick McManus
Dick McManus

Reputation: 789

Answered elsewhere by suyash soni:

Simple Solution

You can simply create your own formula instead of going with pre-defined function for "week of the year" Advantage: you will be able to take any set of 7 days for a week.

In your case since you want the week should start from Sunday-Saturday we will just need the first date of sunday in a year

eg- In 2016, First Sunday is on '2016-01-03' which is 3rd of Jan'16 --assumption considering the timestamp column in the format 'yyyy-mm-dd'

SELECT
count(distinct UserId), lower(datediff(timestamp,'2016-01-03') / 7) + 1 as week_of_the_year 
FROM table.data 
where timestamp>='2016-01-03'
group by lower(datediff(timestamp,'2016-01-03') / 7) + 1;

Upvotes: 0

vishwadeep pratap singh
vishwadeep pratap singh

Reputation: 84

for start with sunday put below code

SELECT 
Month(timestamp), Week(timestamp), COUNT (DISTINCT userid) 
FROM Data 
Group By Month, Week

for start with Monday put this

SELECT 
Month(timestamp), Week(timestamp,1), COUNT (DISTINCT userid) 
FROM Data 
Group By Month, Week

Upvotes: 1

Related Questions