CODEWITHSUNDEEP
apachehive
user3279189
user3279189

Reputation: 1653

Hive date function to achieve day of week

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.

Upvotes: 20

Views: 75249

Answers (7)

jaya kumar
jaya kumar

Reputation: 1

Select date_format(current_date, 'EEEE');

Which gives Sunday, Monday, Tuesday etc

Upvotes: 0

user1311888
user1311888

Reputation: 793

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

Upvotes: 4

nessa.gp
nessa.gp

Reputation: 1864

From Hive 2.2 there is another possibility:

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

Upvotes: 0

Peter
Peter

Reputation: 131

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)

Upvotes: 13

iggy
iggy

Reputation: 722

You can now use date_format (Hive 1.2):

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

Upvotes: 24

user3731880
user3731880

Reputation: 469

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.

Upvotes: 46

Mukesh S
Mukesh S

Reputation: 2896

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...!!!

Upvotes: -8

Related Questions