Hi Team- I’m in the process of creating a real-time operations data dashboard using AMG (Amazon Managed Grafana). I’m working on implementing a dropdown feature that allows users to choose a time period from options like 1 minute, 10 minutes, 30 minutes, 1 hour, and more.
I’ve set up a variable with the type “intervals,” but I’m encountering challenges in creating the appropriate logic for it because many SQL functions are not available in AMG.
The dataset I’m working with includes fields such as MetricName (text), Intervals (timestamp in the “hh:mm:ss” format), Date (date), and Value.
Query:
SELECT metric, interval, date, value
FROM nikegen2_cloudwatch_data_prod_v1
WHERE
(
(STR_TO_DATE(interval, '%Y-%m-%d %H:%i:%s') >= DATE_SUB(NOW(), INTERVAL
CASE
WHEN :$SelectPeriod = '1m' THEN 1 MINUTE
WHEN :$SelectPeriod = '10m' THEN 10 MINUTE
WHEN :$SelectPeriod = '30m' THEN 30 MINUTE
WHEN :$SelectPeriod = '1h' THEN 1 HOUR
WHEN :$SelectPeriod = '6h' THEN 6 HOUR
WHEN :$SelectPeriod = '12h' THEN 12 HOUR
WHEN :$SelectPeriod = '1d' THEN 1 DAY
WHEN :$SelectPeriod = '7d' THEN 7 DAY
WHEN :$SelectPeriod = '14d' THEN 14 DAY
WHEN :$SelectPeriod = '30d' THEN 30 DAY
END)
)
OR
(STR_TO_DATE(interval, '%Y-%m-%d %H:%i:%s') >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND :$SelectPeriod = '1d')
)
ORDER BY interval DESC;