使用psql在grafana中按月组织数据

Question

所以我试图创建一个条形仪表板，我的代码是正确的，唯一的问题是给出的结果是按月份名称，而在仪表板中，它没有按正确的顺序组织，而是按字母顺序组织结果，我希望它们按月份的字母顺序组织(正常顺序如1、2、3…(

这是我的代码：

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

Answer 1

按月份的整数值排序

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

Answer 2

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');

来源：https://www.postgresql.org/docs/current/functions-formatting.html