Sorry, really new to mysql and drawing a bit of a blank on where it start with creating a query.

What are you trying to achieve? 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.

How are you trying to achieve it? I have MySQL table containing columns

policyid int last_used int - epoch timestamp - timestamp.

I put the policy stats every 5mins so end up with this table

I think the logical is to workout the MAX value for last_used per policy. Then just compare that to epoch times ( < 3months, between 6 and 9 months, 12 months) from current_time.

So as a start I tried

select policyid, last_used 
FROM STATS 
WHERE last_used >= 1654867964 UNION 
SELECT policyid,MAX(last_used) 
FROM STATS 
ORDER BY policyid;

But not getting the desired output. Has anyone got any ideas how this could be achieved?

CodePudding user response：

Not entirely clear what are you trying to achieve, but based on

to display the number of policies last_used less than 3 months, between 3 and 6months and over 12 months.

You can use conditional aggregation to count the number of records based on last_used columns.

Try:

select  sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end )  as last_3_months,
        sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ) as between_3_6_months,
        sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end )  as over_12_months
from firewall_policy_stats; 

https://dbfiddle.uk/Y-h60QD2

Note. I added some more data example just for testing purpose. If you want to display 0 instead of null you could use coalesce

select  coalesce(sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end ),0)  as last_3_months,
        coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ),0)  as between_3_6_months,
        coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end ),0)   as over_12_months
from firewall_policy_stats;

https://dbfiddle.uk/R5JuoulF

Edit

That’s pretty close to the output of that I need. However I only want to count the policy id once. So the highest epoch time from all the rows containing the same policy id

select  coalesce(sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end ),0)  as last_3_months,
        coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ),0)  as between_3_6_months,
        coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end ),0)   as over_12_months
from ( select policyid,
              max(last_used) as last_used
       from firewall_policy_stats 
       group by policyid
    ) as tbl;

https://dbfiddle.uk/rJLB3svf