title: hive : group records by time interval
body: <p>Trying to figure out a way to group records for time interval in hive table. Time is in hh:mm:ss string format. I want to group column date and count occurences for one hour time interval.</p>
  <p>Below if the sample table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">txn_dt</th>
  <th style="text-align: left;">txn_tm</th>
  <th style="text-align: left;">Txn_amt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">00:00:15</td>
  <td style="text-align: left;">$50</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">02:00:05</td>
  <td style="text-align: left;">$150</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">02:00:15</td>
  <td style="text-align: left;">$510</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Output should be :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">txn_dt</th>
  <th style="text-align: left;">interval</th>
  <th style="text-align: left;">TXN_count</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">00:00:00</td>
  <td style="text-align: left;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">01:00:00</td>
  <td style="text-align: left;">0</td>
  </tr>
  <tr>
  <td style="text-align: left;">2022-05-01</td>
  <td style="text-align: left;">02:00:00</td>
  <td style="text-align: left;">2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>i am using this query but no luck :</p>
  <p>select txn_dt, (unix_timestamp(txn_tm, 'HH:MM:SS')/3600), COUNT(*) as txn_count FROM TABLE
  GROUP BY txn_dt , FLOOR(UNIX_TIMESTAMP(txn_tm,'HH:mm:ss')/3600)</p>