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.

Answer 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".

Answer 2

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