question How to get the Date of the last day of a week given a time stamp in Hadoop Hive? in Archives of Support Questions (Read Only)

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

Gayathridevi — Tue, 25 Sep 2018 17:14:57 GMT

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

nramanaiah — Tue, 25 Sep 2018 18:05:46 GMT

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?

nramanaiah — Tue, 25 Sep 2018 22:25:45 GMT

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.

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

Gayathridevi — Fri, 28 Sep 2018 14:04:54 GMT

@Naresh P R

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?

nramanaiah — Fri, 28 Sep 2018 14:21:00 GMT

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