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.

You need to write UDF for this. Can you explain your requirement ? Also what is the data type of that column ? — Mukesh S, Apr 10 '14 at 11:56

score 46 · Answer 1 · edited Jun 11 '14 at 22:17

46

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.

edited Jun 11 '14 at 22:17

timgeb

76,762
20
123
145

answered Jun 11 '14 at 21:59

user3731880

469
4
4

It works only from Java 7, on Java 6 it fails (no 'u' option in http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html). – Ivan Klass Aug 21 '14 at 03:40
4

Also, if you want the text version of the weekday (i.e. "Mon", "Tue" etc.), you can use `'E'` in place of `'u'` – willwest Apr 10 '15 at 18:07
1

Monday is 1, Sunday is 7 – Mike S Jan 25 '17 at 18:20
greatful if you could share any thoughts on this – Topde Jul 28 '21 at 22:34
https://stackoverflow.com/questions/68568002/how-to-set-simpledateformat-w-use-monday-as-the-first-day-of-the-week-instead-of – Topde Jul 28 '21 at 22:34

score 24 · Answer 2 · answered Jan 10 '17 at 21:51

24

You can now use date_format (Hive 1.2):

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

answered Jan 10 '17 at 21:51

iggy

662
6
14

score 13 · Answer 3 · edited Mar 13 '17 at 18:11

13

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)

edited Mar 13 '17 at 18:11

OneCricketeer

179,855
19
132
245

answered May 28 '14 at 17:16

Peter

131
3

score 4 · Answer 4 · answered Mar 24 '19 at 01:56

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

score 0 · Answer 5 · answered Jan 08 '19 at 15:13

0

From Hive 2.2 there is another possibility:

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

answered Jan 08 '19 at 15:13

nessa.gp

1,804
21
20

score 0 · Answer 6 · edited May 10 '23 at 18:40

0

Select date_format(current_date, 'EEEE');

Which gives Sunday, Monday, Tuesday etc

edited May 10 '23 at 18:40

Tyler2P

2,324
26
22
31

answered May 10 '23 at 13:28

jaya kumar

1

score -8 · Accepted Answer · answered Apr 10 '14 at 13:31

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

Completely unnecessary to write your own UDF for this. – swdev Feb 03 '15 at 23:18 — swdev, Feb 03 '15 at 23:18

Hive date function to achieve day of week

7 Answers7

Linked