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.

How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

Solved Go to solution

How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

New Contributor
 
1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

Expert Contributor

Using SQL

-- First Day of the week
select date_sub('2018-09-25',pmod(datediff('2018-09-25','1900-01-07'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-23  |
+-------------+--+
-- Last Day of the week
select date_add('2018-09-25',6 - pmod(datediff('2018-09-25','1900-01-07'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-29  |
+-------------+--+

If my answer helped to solve your problem, accept the answer. It might help others in the community.

4 REPLIES 4

Re: How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

Expert Contributor

You can write a custom UDF in Hive to pick any day of the week. You can refer lastDay UDF code as an example

// Code in Custom UDF FirstDayOfWeek
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Calendar calendar = Calendar.getInstance();
calendar.setTime(formatter.parse("2018-09-16")); // Actual Date string column here
calendar.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
System.out.println(calendar.getTime());

Re: How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

Expert Contributor

Using SQL

-- First Day of the week
select date_sub('2018-09-25',pmod(datediff('2018-09-25','1900-01-07'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-23  |
+-------------+--+
-- Last Day of the week
select date_add('2018-09-25',6 - pmod(datediff('2018-09-25','1900-01-07'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-29  |
+-------------+--+

If my answer helped to solve your problem, accept the answer. It might help others in the community.

Highlighted

Re: How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

New Contributor
@Naresh P R

Hi

I tried for my scenario I am getting

Also week must start with monday and end with sunday.

select date_sub('2018-09-12',pmod(datediff('2018-09-12','1900-01-07),7))

2018-09-09 I am getting sep 9 th which is Sunday. but for my scenario the week must start with sep 10 th monday

select date_add('2018-09-12',7 - pmod(datediff('2018-09-12','1900-01-07'),7));

2018-09-16

Re: How to get the Date of the last day of a week given a time stamp in Hadoop Hive?

Expert Contributor

If First day of week should start from Monday, change the subtraction/addition date to 1900-01-08

--First day of the week as Monday
select date_sub('2018-09-12',pmod(datediff('2018-09-12','1900-01-08'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-10  |
+-------------+--+
--Last day of the week as Sunday
select date_add('2018-09-12',6 - pmod(datediff('2018-09-12','1900-01-08'),7));
+-------------+--+
|     _c0     |
+-------------+--+
| 2018-09-16  |
+-------------+--+
Don't have an account?
Coming from Hortonworks? Activate your account here