Mysql数据获取时间错误

Question

我在mysql数据库上有一些记录，我试图按月份分组，数据是正确的，但三月的时间显示为"2022-03-22">，我要求与其他月份相同，如2022-03-01。

time                   month_name  inc_number
2022-01-04 19:58:09     January     39393
2022-02-08 17:36:33     February    90203
2022-03-22 13:40:48     March       82923
2022-04-01 00:14:33     April       23333
2022-05-01 00:31:58     May         33322
2022-06-06 17:21:29     June        33244
2022-07-01 04:19:20     July        90283
2022-08-01 00:07:04     August      8428
2022-09-01 09:40:15     September   10097
2022-10-01 00:30:19     October     6421
2021-12-01 07:12:30     December    8521

我使用的查询低于

SELECT
created_on as 'time',
MONTHNAME(created_on) AS 'month_name',
count(distinct id_number) AS "inc_number"
FROM test_reports
WHERE
MONTH(created_on)
GROUP BY MONTH(created_on)
ORDER BY MONTH(created_on)

请建议获得所有时间的方式应该是每个月的第一天。

Answer 1

如果使用GROUP BY而不指定列，MySQL将只使用"array"中的一个created_on值。相反，您应该定义created_on的预期输出，并将其添加到GROUP BY您可以使用类似DATE_FORMAT(created_on, '%Y-%m-01')的东西来始终显示当月的第一天

Answer 2

工作，感谢@verhie

SELECT
created_on as 'time',
MONTHNAME(created_on) AS 'month_name',
count(distinct id_number) AS "inc_number"
FROM test_reports
WHERE
MONTH(created_on)
GROUP BY DATE_FORMAT(created_on, '%Y-%m-01')
ORDER BY MONTH(created_on)