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.

Answer 1

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.

Answer 2

You can now use date_format (Hive 1.2):

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