如何使用Datetime列统计每日创建的行数

核心逻辑：先去除datetime类型值的时分秒、时区偏移部分得到纯日期值，再按该日期分组统计行数即可，以下是不同主流数据库的实现代码：

MySQL #

-- 若需统一按-05:00时区统计日期
SELECT 
    DATE(CONVERT_TZ(CreatedDatetime, @@session.time_zone, '-05:00')) AS `Date`,
    COUNT(*) AS RowsCreatedOnDate
FROM 你的表名
GROUP BY `Date`
ORDER BY `Date`;

-- 若表中存储的CreatedDatetime已统一使用业务统计时区，可简化为
SELECT 
    DATE(CreatedDatetime) AS `Date`,
    COUNT(*) AS RowsCreatedOnDate
FROM 你的表名
GROUP BY `Date`
ORDER BY `Date`;

PostgreSQL #

SELECT 
    DATE(CreatedDatetime AT TIME ZONE '-05:00') AS "Date",
    COUNT(*) AS "RowsCreatedOnDate"
FROM 你的表名
GROUP BY "Date"
ORDER BY "Date";

SQL Server #

SELECT 
    CAST(CreatedDatetime AS DATE) AS [Date],
    COUNT(*) AS [RowsCreatedOnDate]
FROM 你的表名
GROUP BY CAST(CreatedDatetime AS DATE)
ORDER BY [Date];

Oracle #

SELECT 
    TRUNC(CreatedDatetime) AS "Date",
    COUNT(*) AS "RowsCreatedOnDate"
FROM 你的表名
GROUP BY TRUNC(CreatedDatetime)
ORDER BY "Date";

注意事项 #

• 统计前需确认datetime值的存储时区和业务要求的统计时区是否一致，避免跨零点的时间被归类到错误日期
• 代码中你的表名需替换为实际业务表的名称

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