SQLite中'2021-07-31 13:53:26'格式日期如何实现过滤与年月分组统计

问题原因 #

你遇到的返回null问题核心是SQLite内置时间函数（date/datetime/strftime）对输入格式有严格要求，只要字段值存在以下任意一种情况都会返回null：

• 存在首尾空格、不可见特殊字符
• 日期/月份/小时等数值未补前导零（如2021-7-31而非2021-07-31）
• 时间值本身不合法（如月份为13、日期为32等）
  分组后出现null分组是解析失败的行聚合生成的结果。

解决方案 #

方案1：字符串截取法（最稳妥，兼容绝大多数场景） #

如果你的started_at字段统一为YYYY-MM-DD HH:MM:SS格式，直接截取前7位即可得到YYYY-MM格式的年月，无需调用时间函数，性能更高也不会出现解析null问题：

SELECT 
  SUBSTR(started_at, 1, 7) AS year_month,
  COUNT(*) AS record_count
FROM bike_trip
-- 这里添加你需要的WHERE过滤条件，例如过滤2021年全年的数据
-- WHERE SUBSTR(started_at, 1, 7) BETWEEN '2021-01' AND '2021-12'
WHERE SUBSTR(started_at, 1, 7) LIKE '____-__' -- 过滤非法格式的行
GROUP BY year_month
ORDER BY year_month;

方案2：修正时间函数调用 #

如果需要用到时间函数做更复杂的时间运算，先对字段做trim处理清理空白字符，同时调整格式化符得到带横杠的年月格式：

SELECT 
  strftime('%Y-%m', TRIM(started_at)) AS year_month,
  COUNT(*) AS record_count
FROM bike_trip
-- 这里添加时间过滤条件，例如过滤2021年7月及以后的数据
-- WHERE strftime('%Y-%m', TRIM(started_at)) >= '2021-07'
WHERE strftime('%Y-%m', TRIM(started_at)) IS NOT NULL -- 过滤解析失败的行
GROUP BY year_month
ORDER BY year_month;

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