title: MySQL query firewall policy stats
body: <p>Sorry, really new to mysql and drawing a bit of a blank on where it start with creating a query.</p>
  <p><strong>What are you trying to achieve?</strong>
  To create a simple dashboard with 3 Gauges to display data from firewall policy stats. Basically I want a gauge to display the number of policies last_used less than 3 months, between 3 and 6months and over 12 months.</p>
  <p><strong>How are you trying to achieve it?</strong>
  I have MySQL table containing columns</p>
  <p>policyid int
  last_used int - epoch
  timestamp - timestamp.</p>
  <p>I put the policy stats every 5mins so end up with this table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>policyid</th>
  <th>last_used</th>
  <th>timestamp</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>511</td>
  <td>1662808082</td>
  <td>2022-09-10 12:08:08</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662808562</td>
  <td>2022-09-10 12:16:44</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662809702</td>
  <td>2022-09-10 12:35:04</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662809942</td>
  <td>2022-09-10 12:40:02</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662810302</td>
  <td>2022-09-10 12:45:02</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662810602</td>
  <td>2022-09-10 12:50:02</td>
  </tr>
  <tr>
  <td>511</td>
  <td>1662810842</td>
  <td>2022-09-10 12:55:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1661442932</td>
  <td>2022-08-25 16:55:32</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662808054</td>
  <td>2022-09-10 12:08:08</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662808599</td>
  <td>2022-09-10 12:16:44</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662809702</td>
  <td>2022-09-10 12:35:04</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662809987</td>
  <td>2022-09-10 12:40:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662810298</td>
  <td>2022-09-10 12:45:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662810592</td>
  <td>2022-09-10 12:50:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662810860</td>
  <td>2022-09-10 12:55:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662811198</td>
  <td>2022-09-10 13:00:02</td>
  </tr>
  <tr>
  <td>512</td>
  <td>1662811449</td>
  <td>2022-09-10 13:05:02</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I think the logical is to workout the MAX value for last_used per policy. Then just compare that to epoch times ( &lt; 3months, between 6 and 9 months, 12 months) from current_time.</p>
  <p>So as a start I tried</p>
  <pre class="lang-sql prettyprint-override"><code>select policyid, last_used 
  FROM STATS 
  WHERE last_used &gt;= 1654867964 UNION 
  SELECT policyid,MAX(last_used) 
  FROM STATS 
  ORDER BY policyid;
  </code></pre>
  <p>But not getting the desired output. Has anyone got any ideas how this could be achieved?</p>