Hive date function to achieve day of week

Question

I'm looking for a workaround or hive date functions that gives day of the week ,

Sunday - 1
Monday - 2
Tuesday - 3
Wednesday - 4
Thursday - 5
Friday - 6
Saturday - 7

Requirement in detail : I'm looking for a function that takes date string (YYYYMMDD) as input and outputs the day of the week as per the above table.

Abohm

You need to write UDF for this. Can you explain your requirement ? Also what is the data type of that column ? – Bonheur

Answer 1

As I said you need to write a UDF which will accept a string as parameter and return a string. Inside the UDF you need to do these steps:

1.) Parse the input string using SimpleDateFormat(YYYYMMDD)

2.) Use the Below code to get the day of week:

Calendar c = Calendar.getInstance();
c.setTime(yourDate);
int dayOfWeek = c.get(Calendar.DAY_OF_WEEK);

3.) Use this dayOfWeek value in a case statement to get your weekday String and return that string.

Hope this helps...!!!

Bonheur

Completely unnecessary to write your own UDF for this. – Diatribe

Answer 2

Consider using from_unixtime(your date,'u') - this will return day number of week starting from Monday=1. If your date is not in unixtime format, you can use the following instead:

from_unixtime(unix_timestamp('20140112','yyyyMMdd'),'u')

see: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html for simple date format documentation.

Irita

It works only from Java 7, on Java 6 it fails (no 'u' option in docs.oracle.com/javase/6/docs/api/java/text/…). – Anus

Also, if you want the text version of the weekday (i.e. "Mon", "Tue" etc.), you can use 'E' in place of 'u' – Congress

Monday is 1, Sunday is 7 – Mumps

greatful if you could share any thoughts on this – Naphtha

#68568502 – Naphtha

Answer 3

You can now use date_format (Hive 1.2):

hive> select date_format('2016-12-01' ,'u');
OK
4

Ramakrishna

Answer 4

select pmod(datediff(your_date,'1900-01-07'),7) + 1 as WeekDay from your_table

• arbitrary start date picked (1900-01-07)
• calculates the mod 7 day of week (plus 1 to start at 1 instead of zero)

Gabriellegabrielli

Answer 5

Expanding on iggy's answer, here is the query to get the days of the week. Adjust the query to set the first day of the week as necessary.

SELECT current_date AS `Date`,
       CASE date_format(current_date,'u')
           WHEN 1 THEN 'Mon'
           WHEN 2 THEN 'Tues'
           WHEN 3 THEN 'Wed'
           WHEN 4 THEN 'Thu'
           WHEN 5 THEN 'Fri'
           WHEN 6 THEN 'Sat'
           WHEN 7 THEN 'Sun'
END AS day_of_week

Sordino

Answer 6

From Hive 2.2 there is another possibility:

hive> select extract(dayofweek FROM your_date) FROM your_table;

Laminate

Answer 7

Select date_format(current_date, 'EEEE');

Which gives Sunday, Monday, Tuesday etc

Compulsory