Msql Data fetch getting wrong time-CodePudding

i have some records on mysql db, i tried to group by Month,data is correct but time of March month is showing as "2022-03-22", i required as same as other months like 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

the query im using is below

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)

Please suggest the way to get all time should be first date of each month.

CodePudding user response：

If you use GROUP BY and do not specify a column, MySQL will just use one of the created_on values from the 'array'. Instead you should define the expected output of created_on and add it to your GROUP BY You could use something like DATE_FORMAT(created_on, '%Y-%m-01') to always display the first day of that month

CodePudding user response：

working, thanks @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)