如何按月份统计注册用户数？毫秒时间戳查询无结果求助

你的查询返回空结果的核心原因是：YEAR() 和 MONTH() 函数仅支持日期/时间类型的输入，但你的createDate是存储为BIGINT的毫秒级时间戳（纯数字值），直接传入这些函数会被当作普通整数处理，完全无法匹配2023年的日期范围，自然没有数据返回。

以下是不同数据库场景下的解决办法：

MySQL 解决方案 #

需要先将毫秒时间戳转换为日期类型，MySQL的FROM_UNIXTIME()函数默认处理秒级时间戳，因此要先把毫秒值除以1000：

基础修正版 #

SELECT 
  MONTH(FROM_UNIXTIME(createDate/1000)) AS 月份, 
  COUNT(*) AS 注册总量
FROM users
WHERE YEAR(FROM_UNIXTIME(createDate/1000)) = 2023
GROUP BY MONTH(FROM_UNIXTIME(createDate/1000))
ORDER BY 月份;

大数据量优化版 #

如果用户表数据量较大，直接在WHERE子句使用函数会触发全表扫描。可以提前计算2023年的毫秒时间戳范围，用范围查询利用索引：

• 2023年1月1日0点的毫秒戳：1672531200000
• 2023年12月31日23:59:59的毫秒戳：1704067199999

SELECT 
  MONTH(FROM_UNIXTIME(createDate/1000)) AS 月份, 
  COUNT(*) AS 注册总量
FROM users
WHERE createDate BETWEEN 1672531200000 AND 1704067199999
GROUP BY MONTH(FROM_UNIXTIME(createDate/1000))
ORDER BY 月份;

PostgreSQL 解决方案 #

PostgreSQL使用TO_TIMESTAMP()函数转换秒级时间戳，同样需要先将毫秒值除以1000：

基础修正版 #

SELECT 
  EXTRACT(MONTH FROM TO_TIMESTAMP(createDate/1000)) AS 月份, 
  COUNT(*) AS 注册总量
FROM users
WHERE EXTRACT(YEAR FROM TO_TIMESTAMP(createDate/1000)) = 2023
GROUP BY EXTRACT(MONTH FROM TO_TIMESTAMP(createDate/1000))
ORDER BY 月份;

大数据量优化版 #

用毫秒时间戳范围查询提升效率：

SELECT 
  EXTRACT(MONTH FROM TO_TIMESTAMP(createDate/1000)) AS 月份, 
  COUNT(*) AS 注册总量
FROM users
WHERE createDate BETWEEN 1672531200000 AND 1704067199999
GROUP BY EXTRACT(MONTH FROM TO_TIMESTAMP(createDate/1000))
ORDER BY 月份;

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