CODEWITHSUNDEEP
datehivecalendarhiveqlsimpledateformat
Joe
Joe

Reputation: 3806

convert numeric week of year to a date (yyyy-mm-dd) in hiveql

It is easy enough to extract a week value from a date in hiveql:

select date_format('2020-10-18','w');

Is there a function for reversing this process: extracting the end date when provided a year and week number?

Upvotes: 1

Views: 782

Answers (1)

leftjoin
leftjoin

Reputation: 38335

To get accurate date you need to provide also week day along with year and week number in a year.

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 7', 'yyyy, w, u'), 'yyyy-MM-dd');

Returns:

43   2020-10-18

It looks like week number in a year counted from Sundays and day number in a week is counted from Mondays because Monday is 19th:

select date_format('2020-10-18','w u'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u'), 'yyyy-MM-dd');

returns

43  2020-10-19

If that is true, you can fix it by subtracting 60*60*24 from unix_timestamp:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u')-60*60*24, 'yyyy-MM-dd');

Returns:

43  2020-10-18

UPDATE: Surprisingly, if not providing day in a week, only year and week number, it works also counting Sunday as a week day by default but it will be not correct for other dates for example 2020-01-20, it will return the same Sunday 2020-01-18, check it yourself:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

returns:

43  2020-10-18

So, if you do not have day in a week and do not need absolutely accurate date, then use

from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

Or like this (year and week number are selected from the table):

  select from_unixtime(unix_timestamp(concat(col_year, ', ', col_week), 'yyyy, w'), 'yyyy-MM-dd') from your_table;

Upvotes: 1

Related Questions