CODEWITHSUNDEEP
hadoophivehiveql
alwaystrying
alwaystrying

Reputation: 163

Displaying the day name of a previous date in a Hive Query?

I am trying to display the day of a previous date. Please see the below. I am able to do this for the current date, but unable to use the date_sub function to get the day displayed of 3 days ago.

select from_unixtime(unix_timestamp(current_timestamp),'EEEE');
+-----------+--+
|    _c0    |
+-----------+--+
| Thursday  |
+-----------+--+

select from_unixtime(unix_timestamp(date_sub(current_timestamp,3)),'EEEE');
+-------+--+
|  _c0  |
+-------+--+
| NULL  |
+-------+--+

Is there another way of achieving this?

Upvotes: 1

Views: 702

Answers (2)

Jonathan
Jonathan

Reputation: 164

Try this:

select date_format(date_add(to_date(CURRENT_TIMESTAMP()), -3), 'EEEE');

You can change the '3' to the number of days you'd like to go back. Try to avoid using 'unix_timestamp' as that function is being deprecated.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Subtract the number of seconds*num days from unix_timestamp and then use the conversion. 

from_unixtime(unix_timestamp(current_date) - 3*86400,'EEEE')

Upvotes: 0

Related Questions