SQL按月份（月初日期）分组统计并显示为DateTime格式的问题

Hey there! I see the issue with your current query—those weird 2020-11-00 dates are popping up because your date formatting syntax is off. Let's break down what's wrong and fix it so you get the proper month-start timestamps you need.

What's Causing the 00 Day Issue? #

Your original query uses DATE_FORMAT(timestamp, '%Y-%m-dd HH')—the dd here tells MySQL to pull the day value from the original timestamp, but then you're trying to parse that string into a date. Since you don't actually want the original day, this creates invalid date values (there's no 00th day of any month), hence the broken 2020-11-00 result.

Correct Solutions to Get Month-Start Dates #

Here are a few straightforward ways to generate the first day of the month (as a DATETIME type) and group your counts correctly:

1. Direct Date Formatting (Simplest Approach)

Use DATE_FORMAT to explicitly set the day to 01 and time to 00:00:00, then cast it to a DATETIME for strict type consistency:

SELECT 
  CAST(DATE_FORMAT(timestamp, '%Y-%m-01 00:00:00') AS DATETIME) AS month_start,
  COUNT(*) AS count
FROM table_a
GROUP BY month_start;

2. String Concatenation + Parse

If you prefer building the date string manually, concatenate the year-month part with -01 00:00:00 and parse it into a DATETIME:

SELECT 
  STR_TO_DATE(CONCAT(DATE_FORMAT(timestamp, '%Y-%m'), '-01 00:00:00'), '%Y-%m-%d %H:%i:%s') AS month_start,
  COUNT(*) AS count
FROM table_a
GROUP BY month_start;

3. Date Function Calculation (No String Manipulation)

Use MySQL's date arithmetic to compute the first day of the month without touching string formatting:

SELECT 
  DATE_ADD(LAST_DAY(DATE_SUB(timestamp, INTERVAL 1 MONTH)), INTERVAL 1 DAY) AS month_start,
  COUNT(*) AS count
FROM table_a
GROUP BY month_start;

This works by:

• Subtracting 1 month from the original timestamp
• Grabbing the last day of that previous month
• Adding 1 day to land on the first day of the original timestamp's month

Expected Result #

All these queries will return the exact grouping you're looking for:

内容的提问来源于stack exchange，提问作者hinafaya