如何在Laravel中查询每月指定日期范围内的数据库记录

核心思路 #

把存储的Unix时间戳（整数类型）转换成日期格式，提取日期里的「日」部分，判断它是否落在目标区间（比如1日至15日）即可。以下是主流数据库的具体实现：

MySQL 实现 #

用FROM_UNIXTIME()把时间戳转成日期，DAYOFMONTH()提取日部分：

筛选符合条件的记录

SELECT *
FROM your_table_name
WHERE DAYOFMONTH(FROM_UNIXTIME(payment_date)) BETWEEN 1 AND 15;

按年月分组统计数量

SELECT
    YEAR(FROM_UNIXTIME(payment_date)) AS `year`,
    MONTH(FROM_UNIXTIME(payment_date)) AS `month`,
    COUNT(*) AS record_count
FROM your_table_name
WHERE DAYOFMONTH(FROM_UNIXTIME(payment_date)) BETWEEN 1 AND 15
GROUP BY `year`, `month`
ORDER BY `year`, `month`;

PostgreSQL 实现 #

用TO_TIMESTAMP()转换时间戳，EXTRACT(DAY FROM ...)提取日部分：

筛选符合条件的记录

SELECT *
FROM your_table_name
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(payment_date)) BETWEEN 1 AND 15;

按年月分组统计数量

SELECT
    EXTRACT(YEAR FROM TO_TIMESTAMP(payment_date)) AS year,
    EXTRACT(MONTH FROM TO_TIMESTAMP(payment_date)) AS month,
    COUNT(*) AS record_count
FROM your_table_name
WHERE EXTRACT(DAY FROM TO_TIMESTAMP(payment_date)) BETWEEN 1 AND 15
GROUP BY year, month
ORDER BY year, month;

SQL Server 实现 #

通过DATEADD()把Unix时间戳转成日期（假设时间戳单位为秒），DAY()提取日部分：

筛选符合条件的记录

SELECT *
FROM your_table_name
WHERE DAY(DATEADD(SECOND, payment_date, '1970-01-01')) BETWEEN 1 AND 15;

按年月分组统计数量

SELECT
    YEAR(DATEADD(SECOND, payment_date, '1970-01-01')) AS year,
    MONTH(DATEADD(SECOND, payment_date, '1970-01-01')) AS month,
    COUNT(*) AS record_count
FROM your_table_name
WHERE DAY(DATEADD(SECOND, payment_date, '1970-01-01')) BETWEEN 1 AND 15
GROUP BY YEAR(DATEADD(SECOND, payment_date, '1970-01-01')), MONTH(DATEADD(SECOND, payment_date, '1970-01-01'))
ORDER BY year, month;

注意事项 #

如果你的Unix时间戳是毫秒级而非秒级，转换时需要做除法处理：

• MySQL：FROM_UNIXTIME(payment_date / 1000)
• PostgreSQL：TO_TIMESTAMP(payment_date / 1000)
• SQL Server：用DATEADD(MILLISECOND, payment_date, '1970-01-01')替代秒级转换语句

内容的提问来源于stack exchange，提问作者Sergey S.