To get the weekday from TIMESTAMP in hive

Question

I need to calculate mean sales for sunday. Values for the column salesdate(timestamp) are:

2012-01-01 09:00:00
2012-01-01 09:00:00
2012-01-01 09:00:00
...........

I have extracted the date part using to_date().Now how to get weekday(like sunday) from this date in hive? Please guide.

Solution 1

You can use a combination of unix_timestamp, and from_unixtime UDFs.

from_unixtime(unix_timestamp(col), 'EEEE')

If you check the documentation for SimpleDateFormat, which from_unixtime uses, you can see that "EEEE" is the code for the full name of the day of the week. "EEE" gets you the abbreviated version, i.e. "Sun" or "Mon".

Solution 2

In Hive you can also use the below method to solve this problem in very elegant way and its performance is very good.

from_unixtime accepts the 1st argument in int format:

date_format(from_unixtime(col(timestampinseconds),'yyyy-MM-dd'),'EEEE')

You can also test it like this:

select date_format(from_unixtime(1531372789,'yyyy-MM-dd'),'EEEE');

Output:

Thursday

I hope it serves your purpose.

Solution 3

There is no OOTB feature to achieve this as of now. A ticket is open though.

You need to write a UDF for this. Or, you could also try the patch available with the above mentioned ticket.

HTH