title: How to convert from DAYOFWEEK and WEEKOFYEAR to date in Hive
body: <p>I'm trying to <code>GROUP BY</code> to count events over weeks in Hive. What I'd like to get out is the date for each Saturday of the year (the output only needs to return results for weeks where we have data) and the number of events that occurred over the entire preceding week (ie, the <code>num_events</code> column should be the total number of events from Sunday through Saturday). </p>
  
  <p>Example Desired Output:</p>
  
  <pre><code>+------------+------------+
  |  ymd_date  | num_events |
  +------------+------------+
  | 2016-01-09 |         42 |
  | 2016-01-16 |        500 |
  | 2016-01-23 |       1090 |
  | .          |          . |
  | .          |          . |
  | .          |          . |
  | 2016-12-31 |      23125 |
  +------------+------------+
  </code></pre>
  
  <p>But I'm not sure how to convert from <code>WEEKOFYEAR</code> to get the date for each Saturday.</p>
  
  <p>What I Have So Far:</p>
  
  <pre><code>SELECT 
      concat_ws('-', cast(YEAR(FROM_UNIXTIME(time))as string),
          lpad(cast(MONTH(FROM_UNIXTIME(time))as string), 2, '0'), 
          cast(WEEKOFYEAR(FROM_UNIXTIME(time))as string)) as ymd_date,
      COUNT(*) as num_events
  FROM 
      mytable
  GROUP BY 
      concat_ws('-', cast(YEAR(FROM_UNIXTIME(time))as string),
      lpad(cast(MONTH(FROM_UNIXTIME(time))as string), 2, '0'),
      cast(WEEKOFYEAR(FROM_UNIXTIME(time))as string))
  ORDER BY
      ymd_date
  </code></pre>
  
  <p>Example Current Output:</p>
  
  <pre><code>+------------+------------+
  |  ymd_date  | num_events |
  +------------+------------+
  | 2016-01-1  |         42 |
  | 2016-01-2  |        500 |
  | 2016-01-3  |       1090 |
  | .          |          . |
  | .          |          . |
  | .          |          . |
  | 2016-12-52 |      23125 |
  +------------+------------+
  </code></pre>
  
  <p>I think what I have so far is just about there, but the date (the <code>ymd_date</code> column) shows the year-month-weekofyear instead of year-month-day.</p>
  
  <p>Any ideas on how to produce the <code>yyyy-mm-dd</code> for each week? </p>