Organizing data by month in grafana using psql-CodePudding

so im trying to create a Bar gauge dashboard and i have my code right the only problem is that the results given are by Month Name and in the dashboard it is not organized in the right order instead the results are organized in alphabetical order and i wanted them to be organized by months ir cronological order (normal order like 1, 2, 3, ...)

This is my code:

SELECT NOW() as time, TO_CHAR(updated_on, 'Month'), SUM(time_spent_minutes)/COUNT(DISTINCT ticket_id) "Avg"
FROM ticket_messages
WHERE 
    admin_id IN ('20439', '20457', '20291', '20371', '20357', '20235','20449','20355','20488') and
    (updated_on BETWEEN NOW() - INTERVAL '1 YEAR' AND NOW())
GROUP BY TO_CHAR(updated_on, 'Month')
ORDER BY 2

CodePudding user response：

Order by the integer value of month

SELECT NOW() as time, TO_CHAR(updated_on, 'Month'), SUM(time_spent_minutes)/COUNT(DISTINCT ticket_id) "Avg"
FROM ticket_messages
WHERE 
    admin_id IN ('20439', '20457', '20291', '20371', '20357', '20235','20449','20355','20488') and
    (updated_on BETWEEN NOW() - INTERVAL '1 YEAR' AND NOW())
GROUP BY TO_CHAR(updated_on, 'Month')
ORDER BY TO_CHAR(updated_on, 'mm')::integer

CodePudding user response：

SELECT
    to_char(now(), 'YYYY-MM'),
    to_char(now(), 'YYYY-Mon'),
    to_char(now(), 'YYYY-mon'),
    to_char(now(), 'YYYY-MON'),
    to_char(now(), 'YYYY-MON'),
    to_char(now(), 'YYY-MON'),
    to_char(now(), 'Y,YYY-MON');

source: https://www.postgresql.org/docs/current/functions-formatting.html